SQL Server Permissions – User Cannot Query Cross-Database Despite Having Permissions

permissionssql serversql-server-2017

SQL Server 2017 Enterprise (14.0.3356.20), running on Windows Server 2016.

I have a situation where an application's user needs to join on a table in a separate database in the same instance, but I'm getting permissions errors when I try to run anything cross-database. Basically, what it boils down to is that this works fine:

use DB2
EXECUTE AS user = 'domain\appUser'
SELECT TOP 1 * FROM dbo.Table
REVERT

But this doesn't:

use DB1
EXECUTE AS user = 'domain\appUser'
SELECT TOP 1 * FROM DB2.dbo.Table
REVERT

I get the following error on the second query:

Msg 916, Level 14, State 1, Line 3
The server principal "domain\appUser" is not able to access the database "DB2" under the current security context.

The user is mapped to both databases. There are plenty of places elsewhere in our other applications that we have used this same kind of cross-database join, and they work fine. Logged in as my admin user, this also works fine of course. I admittedly don't know too much about permissions in SQL Server, and this one has me stumped.

Best Answer

This is an artifact of your testing methodology. You impersonated a user. A user is a database-level principal.

Instead impersonate the login, or test with the target identity actually connecting to SQL Server.