Cross-database permissions problem

Question

Can anyone help me with this problem please?

I have 2 databases on the same server, both owned by sa. A Windows login has permission to execute a stored proc in database A that selects data from a table database B. The stored proc and table are also both owned by dbo. The Windows login is a member of a Windows domain group that is a member of a database role in database A that has the permission to execute the stored proc, but it has not been granted or denied any permissions on database B directly. The login is able to execute stored procs in database A that use database A and other procs that access other databases that the login has permissions to already but when it attempts to execute this stored proc it generates the error "The server principal "" is not able to access the database "B" under the current security context." Cross database ownership chaining is enabled at the server level but the two databases have is_db_chaining_on = 0 in sys.databases.

Is it as simple as you have to enable cross-database ownership chaining at both the server and database level?

Answer 1

From memory if you enable cross-database chaining at the server level it is available for all databases, you do not have to specifically set it on the database level.

How did you set chaining on? For some system settings a restart is required, or you can run the RECONFIGURE command in a query window.

http://msdn.microsoft.com/en-us/library/ms176069(SQL.90).aspx