Agent Jobs

Check Database Ownership Chaining

Run this SQL Logic to determine if database ownership chaining is enabled. It checks both the engine configuration, and each database. Remember; some system databases cannot have the configuration set so I have excluded database_id’s of certain system databases. This is completely normal.

This should save you some keystrokes.

use [master];
set nocount on

-- check database chaining engine wide
select 
	'sql_instance'	= @@servername
,	'configuration'	= upper([name])
,	'setting'	= case when [value_in_use] = '1' then 'ON' else 'OFF' end from sys.configurations where [name] = 'cross db ownership chaining'

-- disable ownership chaining for engine
execute sp_configure 'show advanced', 1;  reconfigure;  
execute sp_configure 'cross db ownership chaining', 0;  reconfigure;  

-- disable ownership chaining per database
declare @set_db_chaining_off	varchar(max)
set	@set_db_chaining_off	= ''
select	@set_db_chaining_off	= @set_db_chaining_off + 
'alter database [' + name + '] set db_chaining off;' + char(10)
from	sys.databases where [database_id] > 3
exec	(@set_db_chaining_off)

-- check database chaining per database
select 
	'database'	= upper([name])
,	'db_chaining'	= case when [is_db_chaining_on] = '0' then 'Off' else 'On' end
from	sys.databases where [database_id] > 3 order by [is_db_chaining_on] desc
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s