Cross Database Chaining

Published On: 2012-12-26By:

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


Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner    Microsoft MVP    Microsoft Certified Master    VMWare Partner    VMWare vExpert
   Best Full-Service Cloud Technology Consulting Company    Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link