SQL Server – Cross-Database View: Not Able to Access Database Under Current Security Context

permissionssql serversql-server-2012users

We have two databases, Adb and Bdb. In Bdb, we create a view that references another view in Adb, like so: CREATE VIEW Bdb.dbo.Bview AS SELECT * FROM Adb.dbo.Aview.

We have an SQL Authenticated Blogin, mapped to Buser, both on Adb and Bdb, with at least the db_datareader role on both.

The following doesn't work:

USE Bdb;
EXECUTE AS USER = 'Buser';
SELECT * FROM Bdb.dbo.Bview;
SELECT * FROM Adb.dbo.Aview;

The following error is thrown for both selects:

Msg 916, Level 14, State 1, Line 4
The server principal "Buser" is not able to access the database "Adb" under the current security context.

However, this works:

USE Adb;
EXECUTE AS USER = 'Buser';
SELECT * FROM Adb.dbo.Aview;
SELECT * FROM Bdb.dbo.Bview;

I have noticed that when I first USE Bdb and switch to Buser, I can't see any other databases:

USE Bdb;
EXECUTE AS USER = 'Buser';
SELECT * FROM sys.databases; -- only master, tempdb and Bdb is shown

But, when I USE Adb first, I see all of them, even ones that don't have the Buser and can't be accessed by it:

USE Adb;
EXECUTE AS USER = 'Buser';
SELECT * FROM sys.databases; -- all DBs on the server are shown

What could be causing this issue? What should I check?

Best Answer

First things first: DO NOT ENABLE TRUSTWORTHY!! There is absolutely no reason to open up such a large security hole. (note: msdb has TRUSTWORTHY enabled and that is fine as it is a Microsoft-supplied DB; User created DBs never need TRUSTWORTHY enabled)

Now, if this works when impersonating a User instead of a Login, it is due to your [Adb] Database already being enabled as TRUSTWORTHY ON, which removes the default quarantine that exists when using Database-level Impersonation. You can see this by executing the following:

SELECT db.is_trustworthy_on, *
FROM   sys.databases db
WHERE  db.[name] IN (N'Adb', N'Bdb');

Assuming that it is the case that Adb is enabled for TRUSTWORTHY and Bdb is not, then still please do not enable TRUSTWORTHY for Bdb. It would be best to disable TRUSTWORTHY for Adb and use Module Signing to accomplish this:

ALTER DATABASE [Adb] SET TRUSTWORTHY OFF;

For an example of doing this cross-database access via Module Signing, please see the following answer of mine (here on DBA.SE):

Access view based on table in another database without account in that other database

For more info on why you should use Module Signing instead of TRUSTWORTHY (or even Cross-Database Ownership Chaining), please see the following post of mine:

PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining

For more info on Module Signing in general, please see:

https://ModuleSigning.info/

As @Nic mentioned in a comment on the question, it is best to use EXECUTE AS LOGIN instead of EXECUTE AS USER when testing. Logins are at the Server-level and will have access to Database's that a User has been created in for that Login. This will be just like logging into SQL Server as that account.

The reason for the difference is stated in the Microsoft documentation page for Extending Database Impersonation by Using EXECUTE AS

Understanding Impersonation Scope

...

However, when impersonating a principal by using the EXECUTE AS USER statement, or within a database-scoped module by using the EXECUTE AS clause, the scope of impersonation is restricted to the database by default. This means that references to objects outside the scope of the database will return an error.

Also, there is a lot of good information on the "Extending Database Impersonation by Using EXECUTE AS" MSDN page (linked above) that explains authenticators and the reasoning behind these rules.

Given that these two Databases are vendor-supplied (info added after I submitted this answer), then it is probably best to just switch to EXECUTE AS LOGIN and not make any changes to the Databases (for Module Signing).

Related Question