"Cross database ownership chaining"

I saw this option in SSMS under "Servers, Property, Security tab". I looked at BOL but I still don't understand what it is or what it is used. Any help appreciated.

TIA,

Barkingdog

I think that this is detailed described in the BOL. To summarize that shortly. If one user creates a view or a procedure and grant appropiate permissions for THAT object to some user for Select / Execute and the object accesses objects in other databases which are also owned by the creator of the View/Procedure then with Cross database ownership chain SQL Server assumes that the permissions don′t have to be checked for the accessing user of the view/procedure. if the ownership chain is broken, or the function is disabled permissions are check also for the access to the second database for the accessing user. For this case you would have to grant the user permissions in the second database whereas you would not need to in the first case.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Ownership Chaining

By default, all database objects have owners. When an object such as a view, a stored procedure, or a user-defined function references another object, an ownership chain is established. For example, a table that is owned by the same user. When the same user owns the source object, the view, stored procedure, or user-defined function, and all target objects (underlying tables, views, or other objects), the ownership chain is said to be unbroken. When the ownership chain is unbroken, SQL Server checks permissions on the source object but not on the target objects.

Cross-Database Ownership Chaining

Cross-database ownership chaining occurs when the source object depends on objects in another database. A cross-database ownership chain works in the same way as ownership chaining in a database, except that an unbroken ownership chain is based on all the object owners being mapped to the same login account. Therefore, in a cross-database ownership chain, if the source object in the source database and the target objects in the target databases are owned by the same login account, SQL Server does not check permissions on the target objects.
If you have more than one database used by an application, and that application calls stored procedures or views in a database that is based on objects in another database, then cross-database ownership chaining is used. Applications that rely on cross-database ownership chaining may generate permission denied errors if cross-database ownership chaining option is turned off.

Risks Associated with Cross-Database Ownership Chaining

Microsoft recommends that you disable the cross-database ownership chaining option because of the actions that highly-privileged users can perform:

Database owners and members of the db_ddladmin or the db_owners database roles can create objects that are owned by other users. These objects can potentially target objects in other databases. This means that if you enable cross-database ownership chaining, you must fully trust these users with data in all databases. To identify the members of the db_ddladmin and the db_owners roles in the current database, execute the following Transact-SQL commands: EXEC sp_configure 'Cross DB Ownership Chaining', '1'; RECONFIGURE Configure cross-database ownership chaining at the database level with the new db chaining option for sp_dboption. When this option is set to false, the database cannot participate in cross-database ownership chaining as either the source or the target database. When this option is set to true, the database can participate in a cross-database ownership chain. By default, this option is false for all user databases after you apply SQL Server 2000 SP3. The following command enables cross-database ownership chaining for the Northwind database: EXEC sp_dboption 'Northwind', 'db chaining', 'true' The effects of sp_dboption are manifested only when the sp_configure Cross DB Ownership Chaining option is set to 0. Also, to enable cross-database ownership chaining at the database level, you must enable this option on both the source and the target database. Configuring cross-database ownership chaining by using SQL Enterprise Manager: ? To set this option for all databases, follow these steps: 1. Right-click <server>. 2. Click to select Properties. 3. Click Security. 4. Click to select Allow cross-database ownership chaining in the Ownership chaining section. 5. Click OK. You are prompted to stop and restart the SQL Server services. 6. Click OK . ? To enable this option at the database level, follow these steps: 1. Right-click the <database>. 2. Click to select Properties. 3. Click Options. 4. Click to select Allow Cross Database Ownership Chaining in the Settings section