Sysadmin Privileges Disappear When using database

Question

I have an AD login added to a SQL Server group. The SQL Server group has the sysadmin role.

When the user logs in they connect correctly

account name    type    privilege   mapped login name   permission path
domain\test     user    admin       domain\test         domain\groupwithsysadmin

When they execute a stored procedure in a user database, it errors out for not having execute permissions.

The EXECUTE permission was denied on the object 'SomeFunction', database 'SomeDatabase', schema 'Test'.

If I execute as user in the database, the same things happens.

use SomeDatabase 
GO 
EXECUTE AS USER = 'domain\test';  
select Test.SomeFunction ('ComponentTest')
REVERT; 
GO

The EXECUTE permission was denied on the object 'SomeFunction', database 'SomeDatabase', schema 'Test'.

If I execute as the login it works.

EXECUTE AS login= 'domain\test'; 
select SomeDatabase.Test.SomeFunction ('ComponentTest')
REVERT; 
GO

I am stumped as to why a the login will not carry the sysadmin privileges when using the database.

Answer 1

It's possible you have encountered a limitation of the EXECUTE AS statement. Here's an excerpt of the doc that might be related to your problem:

While the context switch to the database user is active, any attempt to access resources outside of the database will cause the statement to fail. This includes USE database statements, distributed queries, and queries that reference another database that uses three- or four-part identifiers.