Enabling Cross-Database Access in SQL Server (ADO.NET)
Cross-database ownership chaining occurs when a procedure in one database depends on objects in another database. A cross-database ownership chain works in the same way as ownership chaining within a single database, except that an unbroken ownership chain requires that all the object owners are mapped to the same login account. 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.
Off By Default
Starting with SQL 2000 SP3, ownership chaining across databases is turned off by default. Microsoft recommends that you disable cross-database ownership chaining because it exposes you to the following security risks:
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.
Users with CREATE DATABASE permission can create new databases and attach existing databases. If cross-database ownership chaining is enabled, these users can access objects in other databases that they might not have privileges in from the newly created or attached databases that they create.
Enabling Cross-database Ownership Chaining
Cross-database ownership chaining should only be enabled in environments where you can fully trust highly-privileged users. It can be configured during setup for all databases, or selectively for specific databases using the Transact-SQL commands sp_configure and sp_dboption.
SQL Server 2000 SP3 introduced the Cross DB Ownership Chaining option as a security enhancement that allows you to configure cross-database ownership chaining. To selectively configure cross-database ownership chaining in SQL Server 2005 and later, use sp_configure to turn if off for the server. Then use the ALTER DATABASE command with SET DB_CHAINING ON to configure cross-database ownership chaining for only the databases that require it.
Dynamic SQL
Cross-database ownership chaining does not work in cases where dynamically created SQL statements are executed unless the same user exists in both databases. You can work around this in SQL Server 2005 by creating a stored procedure that accesses data in another database and signing the procedure with a certificate that exists in both databases. This gives users access to the database resources used by the procedure without granting them database access or permissions.
External Resources
For more information, see the following resources.
Resource |
Description |
|---|---|
Extending Database Impersonation by Using EXECUTE AS and Cross DB Ownership Chaining Option In SQL Server 2008 Books Online |
Topics describe how to configure cross-database ownership chaining for an instance of Microsoft SQL Server 2008. |
Extending Database Impersonation by Using EXECUTE AS and Cross DB Ownership Chaining Option in SQL Server 2005 Books Online |
Topics describe how to configure cross-database ownership chaining for an instance of Microsoft SQL Server 2005. |
Cross DB Ownership Chaining in SQL Server 2000 Books Online |
Describes how to configure cross-database ownership chaining using the options introduced in SQL Server 2000 SP3. |
See Also
Concepts
Managing Permissions with Stored Procedures in SQL Server (ADO.NET)
Writing Secure Dynamic SQL in SQL Server (ADO.NET)
Signing Stored Procedures in SQL Server (ADO.NET)