Export (0) Print
Expand All

Cross DB Ownership Chaining

SQL Server 2000

  New Information - SQL Server 2000 SP3.

Use the Cross DB Ownership Chaining option to configure cross-database ownership chaining for the instance of SQL Server. This security enhancement was added in SQL Server 2000 SP3 to allow you to manage cross-database security.

This server option allows you to control cross-database ownership chaining at the database level or to allow cross-database ownership chaining for all databases:

  • When Cross DB Ownership Chaining is off (0) for the instance, cross-database ownership chaining is off. You can turn on cross-database ownership chaining for individual databases using sp_dboption.

    By default all user databases have cross-database ownership chaining turned off. Cross-database ownership chaining is on for the master, tempdb, and msdb system databases. You cannot change cross-database ownership chaining for the master, msdb, model, and tempdb system databases.

  • When Cross DB Ownership Chaining is on (1) for the instance, cross-database ownership chaining is on for all databases. This is equivalent to pre-SP3 functionality.

    Setting this option to 1 is not recommended unless all of the databases hosted by the instance of SQL Server must participate in cross-database ownership chaining and you are aware of the security implications of this setting. For more information, see Using Ownership Chains.

Controlling Cross-Database Ownership Chaining

Before turning cross-database ownership chaining on or off, consider the following:

  • You must be a member of the sysadmin role to turn cross-database ownership chaining on or off.

  • Before turning off cross-database ownership chaining on a production server, fully test all applications, including third-party applications, to ensure that the changes do not affect application functionality.

  • You can change the Cross DB Ownership Chaining option while the server is running if you specify RECONFIGURE with sp_configure.

  • If you have databases that require cross-database ownership chaining, the recommended practice is to set the Cross DB Ownership Chaining option to 0 and to turn on cross-database ownership for any database that must participate in cross-database ownership chaining.

    For example, if Cross DB Ownership Chaining is currently set to 1 (allowed for all databases), you can run the following statements to turn off cross-database ownership chaining for all user databases, and then turn it on for the Northwind and Pubs database:

    USE master
    EXEC sp_configure 'Cross DB Ownership Chaining', '0'; RECONFIGURE
    EXEC sp_dboption 'Northwind', 'db chaining', 'ON'
    EXEC sp_dboption 'Pubs', 'db chaining', 'ON'
    

To configure cross-database ownership chaining

Transact-SQL

See Also

Setting Configuration Options

sp_configure

RECONFIGURE

sp_dboption

Using Ownership Chains

Show:
© 2014 Microsoft