Cannot grant permission on a specific login

Question

Hi All,

I am having a very weird issue.

1. I first found an issue which showing:

The server principal "ReaderLogin" is not able to access the database "RefDB" under the current security context.

2.  Checked the code, which is similar to this:

use databaseA

EXECUTE AS USER = 'ReaderLogin'

select sth from RefDB..tablex

3. Then I login as Reader

use databaseA

select current_user

ReaderLogin

SELECT * FROM fn_my_permissions(NULL, 'SERVER');  

entity_name subentity_name permission_name
server CONNECT SQL
server VIEW ANY DATABASE

USE [RefDb];  
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');  

entity_name subentity_name permission_name
database CONNECT
database SELECT

4. All look right, then I tried to simulate the code in app.

use database1

EXECUTE AS USER = 'ReaderLogin'

select current_user

ReaderLogin

SELECT * FROM fn_my_permissions(NULL, 'SERVER');  

--return nothing
USE [RefDb];  

Msg 916, Level 14, State 1, Line 7

The server principal "ReaderLogin" is not able to access the database "RefDB" under the current security context.

5. we have multiple systems who has the same access to RefDb, but they look good... 

use databaseB
EXECUTE AS USER = 'ReaderLoginB'
select current_user

ReaderLoginB

SELECT * FROM fn_my_permissions(NULL, 'SERVER');  

entity_name subentity_name permission_name
server CONNECT SQL
server VIEW ANY DATABASE

USE [RefDb];  
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');  

entity_name subentity_name permission_name
database CONNECT
database SELECT
database EXECUTE

This system B works fine...

could anyone can pinpoint what I miss here? why that login does not work? I have already tried to recreated the login, grant dbreader role for that refDB...

Answer 1

Here is the SQL Server version:

Microsoft SQL Server 2014 (SP2-CU3) (KB3204388) - 12.0.5538.0 (X64) 
Dec 15 2016 17:52:48 
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

Answer 2

Check directly in the DB.

DB==>Users and see what is set for that login.

Please mark as answer if this post helped you

Answer 3

when login admin, I tried below, still same outcome.

grant CONNECT SQL to ReaderLogin

grant view any database to ReaderLogin

Answer 4

could anyone can pinpoint what I miss here? why that login does not work? I have already tried to recreated the login, grant dbreader role for that refDB...

Once you run EXECUTE AS USER, the session security context is sandboxed such that other databases are accessed under the guest user security context. To test security for ReaderLogin from SSMS, use EXECUTE AS LOGIN = 'ReaderLogin'. That login will need to have a database user for the databases used and the user granted the permissions required by your application.

---

Dan Guzman, Data Platform MVP, http://www.dbdelta.com

Answer 5

Thanks Dan for your prompt reply.

How does other users work in that same way... this has been working well so far until today we see this error.

Answer 6

same as other users.

when look at from GUI, there is no difference.

Answer 7

As Dan mention, when you use EXECUTE AS USER, you are sandboxed into the current database. It is however possible to get out of the sandbox. For this two doors have to be opened:

1) The database must be marked as TRUSTWORTHY.
2) The database owner must have AUTHENTICATE permission in the other database. (For server-level permission, the database owner needs the permission AUTHENTICATE SERVER.)

If the database is owned by sa or has the same owner as the other database, the second door is already open. My guess is that this is the case, and that someone recently flipped the TRUSTWORTHY bit to be off.

TRUSTWORTHY is generally considered a security risk, because used casually it can permit a person who has db_owner permission to elevate his rights to sysadmin. But if you are careful to make sure that your database owners are SQL logins with no other permission, it can be an acceptable solution for cross-database access (but never server-level permission). Although, if all you do is EXECUTE AS, it may be better to do EXECUTE AS LOGIN.2

Answer 8

How does other users work in that same way... this has been working well so far until today we see this error.

I see different logins/users and databases in your script so it may be that the working databases marked TRUSTWORTHY as Erland suspects.

I'm generally not a big fan of using EXECUTE AS in application code but I don't know enough about your application to make specific suggestions as to a better approach.

---

Dan Guzman, Data Platform MVP, http://www.dbdelta.com

Answer 9

Thanks Dan,

you are right, I just checked trustworthy on RefDB...

Now I cross checked all databases found these 2 databases have trustworthy disabled... after turning them on, it works fine now,Thanks!

Answer 10

Again, please beware that TRUSTWORTHY can be a big security risk!

But if you know for sure that no one will ever be db_owner in a trustworthy database without being sysadmin on server level, you are good.