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
