SQL Server with Mr. Denny

Dec 26 2012   2:00PM GMT

Cross Database Chaining

Denny Cherry Denny Cherry Profile: Denny Cherry

Cross database chaining in SQL Server is actually a fairly old feature, first introduced in SQL Server 2000 SP3.  However this feature isn’t often understood mostly because it isn’t often used.

Database chaining is when permissions cascade from one object to another because they are used by the parent object.  The perfect example is a stored procedure which accesses a table.  The user only needs rights to the parent object (the stored procedure) and the rights to access the table exist automatically because the stored procedure accesses the child object (the table).

Cross database chaining uses this exact same concept except that the parent object is in one database and the child object is in another database.  In order to use cross database chaining the feature needs to be enabled on both databases.  This is done by using the ALTER DATABASE statement as shown below on both databases.

ALTER DATABASE A_Database SET DB_CHAINING ON

Once this is done, the login which is mapped to the user within the database which has the parent object needs to be mapped to a login within the database which has the child object.  The user within the database which owns the child object doesn’t need any specific rights other than to be a member of the public role.  Once this is done the cross database permission chain will be made and the stored procedure (or other parent object such as a trigger or function) will begin working.

Denny

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: