Cross Database Ownership Chaining

Cross Database Ownership Chaining is a mechanism of Microsoft SQL Server to grant a database 'A' user access to objects in databases B where he doesn’t explicitly got access-permissions to.
Note: By default Cross-database ownership chaining is turned ON for the master, temp db, and msdb system databases. You cannot change cross-database ownership chaining for the master, msdb, model, and tempdb system databases.
 By default you’re already using Cross Database Ownership Chaining on all of your SQL Servers. You surely already used a function or table of the master\temp-database. If you take a look at the security-configuration of the master-\temp database you will see that you’re mostly not explicitly granted access. That’s because Cross Database Ownership Chaining is applied in this operation.


Below Conditions should satisfy before enabling the Cross Database Ownership Chaining:

1.The user must be granted access to database A:As always if we grant access to objects in a database, the user must have access-permissions on the database he directly accesses. In this case it is database A.The user must be granted access to database A.
2.The Databases must belong to the same owner: The databases must belong to the same owner, because Cross Database Ownership chaining assumes that the owner of the database is the only person who can decide whether a database can be accessed from another one or not.
3.The objects must belong to the same owner: The object in database A must have the same owner like the object in database B on that it likes to get access to. SQL Server assumes that the person who developed both objects is the only person who knows whether the object can be accessed from outside.Possibly your objects are not in the default-schema “dbo”. So check the owner of your custom schema whether it is the same as the accessing object in database A.
4.The user must be known by database B:Either the guest-account should be activated on database B or the user must be explicitly added to the security-logins of database B. In both ways the users (guest or the user itself) only need the guest-database-role. This role isn’t enough to access database-objects. This can only be done by the Cross Database Ownership Chaining. We recommend activating the guest-account, because the administration-effort is much smaller. Activate the guest-account using the following command:


Explanation: How it works
we created two databases name like Original_Database and Chained_Database.
again we created one stored procedure in Original_Database. That SP refers to an table object in a second database when return results.
USE Original_Database
CREATE PROC dbo.QueryATable
AS
BEGIN
SELECT TableID FROM Chained_Database.dbo.ATable;
END;
we created one table in Chained_Database
USE Chained_Database
CREATE TABLE dbo.ATable (TableID INT);
we created one login "TestChain" and grant DBO access to Original_Database.. but I did not grant any access on Chained_Database.
Now am going to execute SP from Original_Database.am getting below error cos TestChain account does not have access on Chained_Database objects and still we are not enabled cross database ownership chaining option.
Msg 229, Level 14, State 5, Procedure QueryATable, Line 4
The SELECT permission was denied on the object 'ATable', database 'Chained_Database', schema 'dbo'.


Solution: How to get the results
way1: Grant appropriate access to Testcahin account on Chained_Database. and execute the  SP from Original_Database.It will returns the results.
Way2:
1.Make sure both database owner should be same.
2.Make sure both database object owner should be same.
3.Enable the Gust user account on target database i.e Chained_Database.[GRANT CONNECT TO GUEST]
<or> The user "TestChain" must be explicitly grant access to the Chained_Databass.[Just connect access].
 Note: In both ways the users (guest or the user itself) only need the guest-database-role. This role isn’t enough to access database-objects.
4 .Enable Cross DB ownership chaining on both database.
ALTER DATABASE Original_Database SET DB_CHAINING ON;
ALTER DATABASE Chained_Database SET DB_CHAINING ON;
5. execute the  SP from Original_Database.It will returns the results.
cos the server first checks to see whether cross-database chaining is enabled between Database 1 and Database 2. If cross-database chaining is enabled, the server will check the ownership of the table. is the ownership of database and DB objects are same then , permissions on this table are not checked. The requested information is returned.
Note: If we create same table in Original_Database it self  and execute the SP.. then server first check the object owners ,If object owners are same then server does not check the permission on individual table.[even we don't have access on table]


How to enable\Disable Cross Database Ownership Chaining at instance level:
When Cross DB Ownership Chaining is on (1) for the instance, cross-database ownership chaining is on for all databases by default. No need to enable at individual database level again..
--By using below query we can get the status of Cross Database Ownership Chaining at instance level .If the value is 0, it is off and if the value of 1 indicates it is on at the server level.
SELECT [name], value 
FROM [sys].configurations
WHERE [name] = 'cross db ownership chaining';
-- By using below query we Enable Cross Database Ownership Chaining at instance level
EXECUTE sp_configure 'show advanced', 1;
RECONFIGURE;
EXECUTE sp_configure 'cross db ownership chaining', 1;
RECONFIGURE;


How to enabl\Disable Cross Database Ownership Chaining at individual database level :
--By using below query we can get the status of Cross Database Ownership Chaining at database level .If the value is 0, it is off and if the value of 1 indicates it is on at the server level.
SELECT [name] AS [Database], [is_db_chaining_on]
FROM [sys].databases
ORDER BY [name];
-- By using below query we Enable Cross Database Ownership Chaining at individual database level
EXEC sp_dboption [DB Name], 'db chaining', 'true'
<or>
ALTER DATABASE [DB Name] SET DB_CHAINING ON/OFF;