Cross-Database Ownership Chaining

Ownership chaining allows the management of multiple objects by setting permission on one object and then allowing the chain to control pass through access.

When an object is accessed through a chain, SQL Server first compares the owner of the object to the owner of the calling object. This is the previous link in the chain. If both objects have the same owner, permissions on the referenced object are not evaluated.

Full example of Ownership chaining here

SQL Server can be configured to allow ownership chaining between specific databases or across all databases inside a single instance of SQL Server. Cross-database ownership chaining is disabled by default, and should not be enabled unless it is specifically required.

Server Level

By setting the option at the server level, any settings for this option at a database level are ignored.

--Set instance wide database chaining
sp_configure 'cross db ownership chaining', 1
go
reconfigure with override
go

From the GUI, the option can be accessed from Server Properties | Security

image

Database Level

The DB_CHAINING option Controls whether the database can be accessed by external resources, such as objects from another database.

--set chaining for a database
alter database DB_MONITORING set DB_CHAINING ON
GO

From the GUI, the option can be accessed from

Database Properties | Options | Miscellaneous | Cross-Database Ownership Chaining Enabled (TRUE | FALSE )

image

The eagled eyed may notice that the Cross-Database Ownership Chaining option is greyed out. Im not sure why this is and tried to research further, but it seems that setting the option can only be done at a database level through the ALTER statement.

Advertisements

2 Responses to Cross-Database Ownership Chaining

  1. Gary Melhaff says:

    oh man, you saved me much frustration with the alter database xxx set db_chaining on! I found many sites discussing the option at the database level including msdn but they all say to use sp_configure which doesn’t work!!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: