Search code examples
sql-servert-sqlimpersonationexecute-as

SQL Server EXECUTE AS misbehavior

I have a user which I gave grant access to credential using:

GRANT ALTER ANY CREDENTIAL TO userA

When I log in with that user, the following returns me data

SELECT * FROM sys.credentials

I can see that I have the permission on the SERVER level. This can be verified by doing

SELECT * FROM fn_my_permissions (NULL, 'SERVER')

Using another user, say userB, I gave impersonation to userA by doing

GRANT IMPERSONATE ON USER::userA TO userB

In a stored procedure, while being logged as userB, I switch the context to userA

EXECUTE AS user = 'userA'
SELECT user_name() AS ContextUserName
SELECT * FROM fn_my_permissions (NULL, 'SERVER')
SELECT * FROM sys.credentials

SELECT * FROM fn_my_permissions (NULL, 'SERVER') doesn't return me ALTER ANY CREDENTIAL permission.
SELECT * FROM sys.credentials doesn't return me any values.
SELECT user_name() returns me userA however.

Is there anything I've missed here? Why can't I behave like userA and get data from sys.credentials?

Thank you!

Solution

  • Problem is probably, that EXECUTE AS is only granting UserB the permissions of UserA on the database you are executing it, but not the permissions given on the server. That's why SELECT user_name() is showing UserA, but SELECT * FROM fn_my_permissions (NULL, 'SERVER') is not showing the permissions of UserA, but the permissions of UserB.

    Microsoft Docs:

    EXECUTE AS (Transact-SQL) "The scope of impersonation is restricted to the current database. A context switch to a database user does not inherit the server-level permissions of that user."

    sys.fn_my_permissions "SELECT * FROM fn_my_permissions(NULL, 'SERVER'); returns a list of the effective permissions of the caller on the server."