Complicated Permissions

Permissions let you control which users can access what information in your database. But assigning permissions isn't always straightforward, especially when you're working with dependent objects. Dependent objects are objects whose definition depends on another object. They include views, which depend on tables or other views, and stored procedures, which can depend on tables, views, or other stored procedures. If you have permission to access an object, does that mean you also have permission to access the objects it depends on? The answer, of course, is it depends.

Owners in Chains

To understand how permissions on dependent objects work, you have to understand object ownership. In the past several T-SQL Admin columns, I've talked about managing database users, usernames, and object ownership; once you understand those concepts, you're ready to tackle the concept of ownership chains. Simply put, if an object references another object, that relationship is a chain of objects. Because both objects have owners, you can think of the owners of each object as also connected in a chain called an ownership chain. An ownership chain can be broken or unbroken. An unbroken ownership chain is a chain of objects that all have the same owner. If an object's owner changes, the ownership chain is broken. For example, if Sue owns a table and gives Joe SELECT permissions on her table, Joe can create a view dependent on Sue's table, but a user who accesses Joe's view will encounter a broken ownership chain.

Ownership chains can be many levels deep. Stored procedures or functions that access other procedures or functions have a maximum nesting level of 32 in the call stack. In ownership chains that involve views, the limits aren't so precise. When the SQL Server parser encounters a reference to a view in an SQL statement, it usually replaces the view with its underlying T-SQL definition and combines the view's definition with the statement that accesses the view. Sometimes this behavior can result in a subquery when a view definition replaces a table in a FROM clause, and like stored procedures, subqueries also have a nesting limit. Although SQL Server Books Online (BOL) says that the limit is 32, that number depends on many factors; in some cases you can go deeper, and in other cases less deep. In addition, you can reference a limited number of objects in any one query. If you create a view that depends on many different objects — some of which are views that also depend on many different objects, including other views — you might exceed the limit of 256 objects with just one query. It's unlikely, but possible.

Chains and Permissions

So how do ownership chains determine permissions? If all the objects are in the same database, the answer is simple. In an unbroken ownership chain, a user must have permission for only the first object in the chain. If the ownership chain is broken, the user must have additional permissions at the point in the chain where the break occurs. Consider the example in which Joe's view depends on Sue's table. Suppose Joe gives permission to Dan to select from his view. When Dan tries to perform that SELECT operation, SQL Server tries to access Sue's table, which is where the break in the ownership chain occurs. SQL Server checks to see whether Dan has permission on Sue's table. If so, he can retrieve the rows from Joe's view; if not, he'll receive a permissions error. But if Joe creates a view that depends on his own table and gives Dan permission to select from that view, the situation is different. Because the ownership chain is unbroken, SQL Server doesn't recheck permissions on the underlying table, and Dan won't get an error.

This behavior is usually a good thing. It means you can use views and stored procedures as a security mechanism. For example, if Joe's table contains sales data from across the country but his view contains a WHERE clause to access just the sales data from the Western region, he might want Dan to see data only from the West. If Joe had to give Dan SELECT permissions on the table, Dan could see any of the data he wanted. But when he gives Dan access only to the view and lets the view control which rows Dan can see, Joe has a form of row-level security. If someone other than Joe owned the underlying table, Joe would have to give Dan permissions for the entire table, and he'd get no security benefit from having the view.

A similar situation applies to stored procedures. You might have a stored procedure that modifies a crucial table — say, the payroll table. You want users to be able to modify this table only under specific, tightly controlled conditions. If the procedure references the payroll table and encounters a broken ownership chain, stored procedure users would need explicit permissions to modify the payroll table. Granting this permission would mean users could access the table directly without going through the stored procedure and having to meet all the conditions coded into the stored procedure. With an unbroken chain — and no permissions granted explicitly on the table — users can't directly access the table and instead have to use the stored procedure to modify the payroll table.

Limitations of Inheritance

You can think of unbroken ownership chains as implying a kind of inheritance. If a user has permission for an object, permissions to the underlying dependent objects are inherited and don't have to be verified again. However, inheritance primarily applies to object access that you can grant independently. It doesn't apply to actions for permissions that you can never grant. (To learn about an exception to this rule, see the sidebar "One Exception.") For example, only the owner of an object can alter that object. The owner can't grant ALTER permissions to other users. So if Joe's procedure contains an ALTER TABLE command for one of his tables and Joe has given permission to Dan to execute his procedure, Dan will always get the following permission-violation error message:

Server: Msg 3704, Level 16, State 1, Procedure joe_proc, Line 2
User does not have permission to perform this operation on table 'joe_table'.

And Dan will never be able to run a procedure that contains an ALTER for Joe's table. Note that this message isn't the same error message that SQL Server returns for normal permission errors. If Dan tried to directly select from Joe's table, for which he had no permission, he would get this error message:

Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'joe_table', database 'TSQLSolutions', owner 'joe'.

Of course, putting a specific ALTER statement in a procedure is unusual because most ALTER commands execute only once, not repeatedly like most operations you use in a stored procedure. But you might have a procedure that parameterizes the ALTER TABLE statement so that every time the procedure runs, it adds a new column with a specified name and data type. Such a scenario is possible only when you use dynamic execution. And unfortunately, although dynamic execution is possible inside a stored procedure, the permissions for executing the actions inside an EXEC statement are never inherited; you must explicitly assign them.

Here's an example. Joe could create a procedure that lets him add a new column to his table joe1 by using dynamic execution:

CREATE PROC alter_joe1 (@namevarchar(30), @type varchar(30)) ASEXEC ('ALTER TABLE joe.joe1 ADD ' + @name + ' ' + @type)

Even if Joe grants execute permission to Dan, Dan still won't be able to run the procedure because he isn't allowed to perform the ALTER operation on Joe's table. In fact, if the dynamic execute operation contained normal Data Manipulation Language (DML) commands such as SELECT or UPDATE, Dan would get an error even though the command is inside the stored procedure. Any objects referenced inside a dynamic execute must have permissions explicitly granted.

Cross-Database Permissions

So far, I've discussed only ownership chains, objects, and owners in the same database. Different problems arise if you have objects that depend on objects in another database — for example, a view that references a table in another database. How you do determine permissions in this case? The Microsoft article "Object Ownership Chain Checking Across Databases Depends on Database Ownership" (Q272424, http://www.support.microsoft.com) addresses this problem, but incompletely. The article says that SQL Server doesn't check permissions when a stored procedure or view accesses objects in a different database if the owners of the two databases are the same. The article includes a script that's supposed to prove this assertion. However, in the script, not only are the two database owners (DBOs) the same, but the DBOs also own all the objects in question. If I change the script so that non-DBO users own the objects, it doesn't matter that the DBOs are the same; SQL Server checks permissions and permission errors can occur.

Listing 1 shows my revision of the Microsoft script, which only someone in the sysadmin role can execute. This script is an example of a complete repro script. As a SQL Server administrator, you need to be able to create a standalone SQL script that lets someone reproduce problems you might be having. You can use this script to communicate with support engineers or participants in public Help forums on the Internet. The script in Listing 1 is the exact one I used when I reported the problems in the Microsoft article to Microsoft SQL Server support. The revised script includes USE statements and all the necessary CREATE statements. Finally, the script performs a cleanup to leave the system the way it was before you ran the script.

In the script, I first create two databases and four logins. One login will become the owner of one of the databases. Two of the logins will be object owners in different databases. The fourth login will be a non-owner user who will test access to the objects with cross-database dependencies.

Initially, the two databases have the same owner (the sysadmin who is running the script). The login ObjOwner has the username ObjOwnerinDB1 in DB1, and this username owns a table. The ObjOwner login also has the username ObjOwnerinDB2 in DB2 and creates a view that references the table in DB1. Another user has permissions to SELECT from the view in DB2 and has no problems doing so, even after the owner of DB2 changes. Because the owner of the view and the table map to the same login, the ownership chain isn't broken.

The script continues by changing the owner of DB2 back to the original (sysadmin) owner — you can fill in the name of the original login that's running the script. The owners of DB1 and DB2 should now be the same. But the script adds a new user in DB2 who has the name ObjOwnerinDB1, then changes the owner of the view to this user. Now, when a user other than the owners of the objects or databases tries to select from the view, that user gets a permissions error. Contrary to what the Microsoft article implies, this error occurs even though the DBOs are the same. In my example, the users in both databases who owned the objects had the same name (ObjOwnerinDB1) but mapped to different login names. This example shows that SQL Server isn't simply performing a string comparison of names to make sure the ownership chain is unbroken but is actually checking the SIDs to see who the user really is. If the users are the same login, the ownership chain is unbroken and SQL Server doesn't check permissions in the other database. So the rules for ownership chains are the same whether all the objects are in one database or multiple databases.

Recommendations

The simplest way to avoid problems with broken ownership chains is to have the login sa own all your databases and have the username dbo own all the objects in each database. If other logins need database ownership privileges, you can put those usernames into the db_owner role. If this setup isn't possible, you might need to spend a lot of time making sure everyone has exactly the permissions they need on each object — and you might end up having to grant more explicit permissions than you're comfortable with.