Cross database chaining on system databases.....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SQL Server Administration (2012)

Cross database chaining on system databases.....

Author

Topic

bulump
Starting Member

6 Posts

Posted - 2014-01-02 : 16:39:21

MS SQL Server 2008 R2

Ok, so I've been under the impression that you couldn't turn db_chaining off on the system databases. Maybe I'm confusing myself, but this seems a little misleading:

First I check the initial setting:


select name, suser_sname(owner_sid) as owner,is_db_chaining_on chaining from sys.databases a where db_name() = 'master'

name	owner	chaining
master	sa	1

Here I attempt to alter the server level settings for Cross DB Chaining


USE MASTER;
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'cross db ownership chaining', 0;
GO
RECONFIGURE;
GO

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'cross db ownership chaining' changed from 1 to 0. Run the RECONFIGURE statement to install.

RECONFIGURE;
Command(s) completed successfully

Note on how the change from 1 to 0 was successful, thus insinuating that disabling db_chaining on master was successful. However, when I rerun the query above, it shows:


select name, suser_sname(owner_sid) as owner,is_db_chaining_on chaining from sys.databases a where db_name() = 'master'

name	owner	chaining
master	sa	1

And when I run a query from the sys.configuration table I get:


select db_name(), name, CAST(value as int) as value_configured, CAST(value_in_use as int) as value_in_use from sys.configurations where name like ('Cross db ownership%')

(No column name)	name	                        value_configured	value_in_use
master	                cross db ownership chaining	0	                0

So, what is going on here? One system view says that it is enabled when I successfully disabled it, and the other query show that it is in fact disabled.

When I try to change the chaining on the database level, I get:


EXEC sp_dboption  master, 'db chaining', 'false'

Msg 5600, Level 16, State 2, Line 1
The Cross Database Chaining option cannot be set to the specified value on the specified database.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
sp_dboption command failed.

which is what I would expect.

HELP!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources