DB_CHAINING is disabled by default on new databases because you need to be aware what it exactly do and does it violates some security policy in your system before you explicitly enable it.

DB_CHAINING은 시스템의 일부 보안 정책을 위반하므로 기본적으로 비활성화 되어있다.

-- Create two databases and a login that will call procedure in one database

CREATE DATABASE PrimaryDatabase;

GO

CREATE DATABASE SecondaryDatabase;

GO

CREATE LOGIN TheLogin WITH PASSWORD = 'Very strong password!'

GO

-- Create one database with some data table,

-- and another database with a procedure that access the data table.

USE PrimaryDatabase;

GO

CREATE PROC dbo.AccessDataTable

AS

BEGIN

SELECT COUNT(*) FROM SecondaryDatabase.dbo.DataTable;

END;

GO

CREATE USER TheUser FOR LOGIN TheLogin;

GO

GRANT EXECUTE ON dbo.AccessDataTable TO TheUser;

GO

USE SecondaryDatabase;

GO

SELECT * INTO dbo.DataTable FROM sys.objects;

GO

CREATE USER TheUser FOR LOGIN TheLogin;

GO

EXECUTE('SELECT * FROM SecondaryDatabase.dbo.DataTable') AS LOGIN = 'TheLogin' ;

GO

-- Msg 229, Level 14, State 5, Line 34

-- The SELECT permission was denied on the object 'DataTable', database 'SecondaryDatabase', schema 'dbo'.

EXECUTE('EXEC PrimaryDatabase.dbo.AccessDataTable') AS LOGIN = 'TheLogin' ;

GO

--Msg 229, Level 14, State 5, Procedure dbo.AccessDataTable, Line 5 [Batch Start Line 65]

--The SELECT permission was denied on the object 'DataTable', database 'SecondaryDatabase', schema 'dbo'.

ALTER DATABASE PrimaryDatabase SET DB_CHAINING ON;

GO

ALTER DATABASE SecondaryDatabase SET DB_CHAINING ON;

GO

EXECUTE('EXEC PrimaryDatabase.dbo.AccessDataTable') AS LOGIN = 'TheLogin' ;

EXECUTE('SELECT * FROM SecondaryDatabase.dbo.DataTable') AS LOGIN = 'TheLogin' ;

GO

--Msg 229, Level 14, State 5, Line 54

--The SELECT permission was denied on the object 'DataTable', database 'SecondaryDatabase', schema 'dbo'.