T-SQL Script to enable/disable Cross-Database Chaining

0
81

T-SQL script to enable Cross Database Chaining in Microsoft SQL Server through query analyzer:

Use [MY_DATABASE_NAME]
GO
ALTER DATABASE [MY_DATABASE_NAME]
SET DB_CHAINING ON
GO
EXEC sp_grantdbaccess 'GUEST'
GO
Grant CONNECT to GUEST
GO
Grant SELECT to GUEST
GO

* Use the following command to allow cross-database ownership chaining in all databases:

EXEC sp_configure 'Cross DB Ownership Chaining', '1'; RECONFIGURE

* Configure cross-database ownership chaining at the database level with the new db chaining option for sp_dboption. When this option is set to false, the database cannot participate in cross-database ownership chaining as either the source or the target database. When this option is set to true, the database can participate in a cross-database ownership chain.

EXEC sp_dboption  [MY_DATABASE_NAME], 'db chaining', 'true'

* To find out if DB_Chaining is congifured on which DATABASES on current server instance:
SELECT [name] AS [Database], [is_db_chaining_on] FROM [sys].databases ORDER BY [name]; 

Thanks,
Web Editor

LEAVE A REPLY

Please enter your comment!
Please enter your name here

six + four =