SQL Server Security

Schema and Views

The dbo stands for database owner. It is the schema. Objects can be grouped together under a schema, which makes it easier to manage permissions. Ownership relates to security and permissions. There are lots of permissions, such as select, alter, delete, take ownership and others.

Database administrators may lock down the tables so that certain individuals cannot access them (see them with a select statement).

Suppose someone is not allowed to select two dbo tables that a dbo view is using, but you want that someone to be able to access the view that reads those two tables. Further suppose that the view and the two tables are under the same schema, namely dbo. You can do this and it is a good thing. Why? You have made sure that that someone only sees the columns and rows that they are allowed to see. Perhaps they only see the rows associated with their account and not the other accounts. SQL server does not even check the permissions of the underlying tables if they are in the same schema as the view.

Now suppose one of the tables is using a different schema. Perhaps it is the Employees schema instead of the dbo schema. In this case SQL Server will check the permissions on the table under the Employees schema to see if that person is allowed to view the table and if they are denied access to select that table they will also be denied access to the view.

Ownership Chaining

The above is a very simple example of how security works. To do more research you can have a look at ownership chaining on the Internet. More complex examples are beyond the scope of this introductory discussion. In the above example we have assumed that we are working withing a single database. If we were to access another object in a different database, then SQL Server would indeed check the permissions of each object that the view is accessing and decide accordingly.

Leave a comment

Your email address will not be published. Required fields are marked *