We have described, so far, authorization based on a predefined fixed server (determining a set of SQL server-wide privileges) and database (applying to database objects and activities) roles. We have also discussed application roles, which makes the level of permissions independent of those assigned to a SQL Server login or a database user account. Now it is time to look into permissions from the point of view of database objects. There are two main factors that play a role in determining how access rights to them are evaluated - their ownership and custom permissions. We will discuss the first one of these topics in this article and will continue with the other one in the next installment of this series.
Just as with NTFS files and directories, ownership of a database object implies full management rights to it, which includes the ability to grant or deny arbitrarily chosen permissions to any other database user or Windows users and groups, as well as execute any of relevant Transact-SQL statements (such as INSERT, UPDATE, DELETE, SELECT, or EXECUTE). In addition, ownership of an object is granted by default to its creator, although it is possible to manually alter this behavior or transfer ownership to another user (when it comes to our file system analogy, this last option became available natively only recently with the advent of Windows 2003). As far as databases are concerned, they can be created by members of sysadmin and dbcreator roles. Their SQL logins are automatically mapped to the dbo user account of the database, which is also the first member of the db_owner fixed database role. Changing existing database ownership can be handled with the help of sp_changedbowner system stored procedure (starting with SQL Server 2000 Service Pack 3, access to it is limited to members of sysadmin fixed server role). The stored procedure takes @loginame parameter, referring to the SQL login or a Windows account of a new database owner (you can obtain a list of valid logins with sp_helplogins stored procedure). Note that if this account is already mapped to an existing alias or a user account within this database, you will have to remove this mapping first. sp_changedbowner also becomes handy in situations where dbo user has been orphaned, which can happen as the result of deleting a corresponding Windows account or transfer of a database from one Windows domain to another, without a trust relationship between them.
There is also a similar sp_changeobjectowner system stored procedure, which is intended for changing ownership of database objects. The stored procedure takes two parameters: @objectname indicating an existing table, view, user-defined function or stored procedure in the current database, and @newowner, which is a valid database user, database role, or Windows user or group. Keep in mind that sp_changeobjectowner removes all existing permissions from the object, so you might want to store them first, in the form of a script, so they can be applied afterwards. This can be done from the SQL Enterprise Manager interface, by selecting the All tasks option from the database context sensitive menu, then choosing Generate SQL Script... from the secondary menu, picking the object to be scripted, and checking on the Script object-level permissions checkbox on the Options tab. Execution of this stored procedure is limited to members of sysadmin server fixed role as well as db_owner, db_dlladmin, and db_securityadmin fixed database roles.
It is generally recommended to use the dbo user account for object ownership. This simplifies references to it (since qualifying it with the owner's name is no longer required - although doing this is considered as a good practice) and simplifies handling of ownership chaining (discussed next). If this is not feasible in your environment, use database fixed roles or define your own custom ones, and assign ownership to them. Try to avoid granting ownership to database users, otherwise you will have to deal with complications in case these users need to be dropped.
Ownership not only affects privileges of those to which it is assigned, but also has potential impact on how permissions for other database users are determined. This phenomenon is called ownership chaining and takes place in situations where an access to objects in a database is granted via a view, a stored procedure or a user-defined function. Since this involves two or more objects (for example, in cases where a stored procedure references another stored procedure or a view), the way effective permissions are evaluated depends on whether all these objects are owned by the same user. If this is not the case, ownership chain is considered to be broken and permission on each of the objects constituting its links needs to be analyzed separately (i.e. for a user to access the top level object, appropriate permissions need to be granted explicitly for every object where the chain is broken). This not only has some impact on performance but, more importantly, also complicates management and troubleshooting of permissions. If ownership chain is intact (i.e. each of the objects in the chain has the same owner), then all that is required is a permission check on the object directly accessed by the user. Another benefit of such configuration is the ability to prevent direct access to each subsequent object in the chain, forcing clients to use views, stored procedures or user-defined functions instead.
Ownership chaining, however, also has its drawbacks. In particular, it can introduce a potential vulnerability, especially when used across databases. You might be surprised to find out that this is possible, since the set of users is separate for each database, but in this case, what matters are login accounts to which users from various databases are mapped. This means that if objects in two databases are owned by users mapped to the same login stored in sysxlogins table in the master database, then the ownership chain is considered unbroken. Based on the same principle, if the objects are owned by users corresponding to distinct logins, then the ownership chain is broken and permissions on each object need to be evaluated separately. Another case where ownership chain might play a role is a situation where ownership of database objects is assigned directly to Windows users and groups, which creates the possibility of the same Windows account owning objects in two separate databases. As long as the cross-database ownership chaining is enabled, this permits controlling permissions on objects residing in one database via a view, a stored procedure, or a user-defined function located in another.
Starting with SQL Server 2000 Service Pack 3, this feature is configurable and turned off by default (it is turned on in the earlier versions of the product). The first time you are prompted to confirm its settings is during installation of SQL Server 2000 Service Pack 3 (by marking appropriately "Enable cross-database ownership chaining for all databases" option). You can also change it at any point afterwards by running sp_configure stored procedure using the following syntax:
EXEC sp_configure 'Cross DB Ownership Chaining', '1'; RECONFIGURE
Alternatively, you can use the "Allow cross-database ownership chaining" option on the Security tab of the SQL Server Properties dialog box in the SQL Server Enterprise Manager. Either one of these actions effectively enables cross-database ownership chaining for all databases in the current instance of the SQL Server 2000. If the server-wide cross-database ownership chaining is disabled, you have an option of enabling it for individual databases with sp_dboption stored procedure by executing the following statement:
EXEC sp_dboption DBName, 'db chaining', 'true'
where DBName is the name of a target database (you need to run this statement for both databases between which chaining is to be allowed). You can also use the same stored procedure to determine which databases had the default modified.
Note, however that you should avoid using cross database ownership chaining whenever possible as it creates potential security vulnerability. For example, since members of fixed database roles dbo_ddladmin and db_owner can create objects owned by other users (or simply transfer their ownership), they can use this ability as a back door, granting access to objects owned by the same users in another database. The same applies to any logins with CREATE DATABASE permissions, who automatically (by the virtue of being mapped to the dbo user) become members of the db_owner fixed database role.
This concludes discussion on object ownership. In our next article, we will cover management of permissions on database objects and operations.