Cross-database view - Not able to access database under the current security context

Question

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?

• Jonathan Fite: Could Bdb be a contained database? SELECT name, containment_desc FROM sys.databases WHERE name IN ('Bdb', 'Adb')
• Nic: You are executing in the context of the user, try executing instead under the context of the login (bearing in mind that they are different and don't have context outside of the current database. I would expect results using EXECUTE AS LOGIN = 'Buser';

Answer 1

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).

• LowlyDBA - John M: Regarding the first point in more detail: Guidelines for using the TRUSTWORTHY database setting
• Solomon Rutzky: @LowlyDBA Thanks, I will amend my answer to include a note about msdb. But outside of Microsoft supplied databases, there is never a good / valid reason to enable TRUSTWORTHY. Also, I have added a link to a post I just published which is a write-up of a presentation I give on what the reasoning is behind this :-), in more detail than MS has ever provided ;-)
• LowlyDBA - John M: Sweet site! Definitely adding to my bookmark list.
• ROAL: So this whole time, the problem was EXECUTE AS USER instead of LOGIN? Damn. These are both vendor supplied DBs on our servers, the B application needs access to some data of the A application, which is why we created some views - to only allow access to what is required. I don't know the actual password of the Buser, which is why I've been using EXECUTE AS to test access. With EXECUTE AS LOGIN, everything works. Thanks for all the info about the other topics as well!
• SqlWorldWide: @SolomonRutzky even you add Buser login as user in both DB it will not work on Bdb database because EXECUTE AS USER cannot read from Adb (nested view). As @Nic mentioned EXECUTE AS LOGIN will work in this case not because of best practice but login will be able to get access to Adb.

Answer 2

Is Bdb a trusted Database?

SELECT name, is_trustworthy_on FROM sys.databases 

To use EXECUTE AS to another DB you need to set up trust between the Databases:

ALTER DATABASE Bdb SET TRUSTWORTHY ON;
GO

However there is a lot of security concerns around doing this, only do this if you actually understand the risks.

You can read more here: https://technet.microsoft.com/en-us/library/ms188304(v=sql.105).aspx https://support.microsoft.com/en-us/help/2183687/guidelines-for-using-the-trustworthy-database-setting-in-sql-server