Search code examples
sql-servert-sqlpermissions

Move back to first EXECUTE AS USER when 2nd one has less permissions

We have two databases: Employees and Sales.

When I open a brand new query editor and run the following script, I get my user (MyDomain\Bob.Smith), which is correct. This user has access to both databases.

select SUSER_SNAME() as user, original_login() as original

user                  original
MyDomain\Bob.Smith    MyDomain\Bob.Smith

I also created user TempUser that only has access to DB Sales.

If I run the following query, I naturally get the error:

The server principal "TempUser" is not able to access the DB "Employees" under the current security context.

EXECUTE AS USER = 'MyDomain\Bob.Smith';
select top 10* From Employees.dbo.NewEmployees

EXECUTE AS USER = 'TempUser'
GO
select top 10* From Employees.dbo.NewEmployees

But if I try to run EXECUTE AS USER = 'MyDomain\Bob.Smith' to go back to the original login, I will get error:

Cannot execute as the database principal because the principal "MyDomain\Bob.Smith" does not exist, this type of principal cannot be impersonated, or you do not have permission.

I have an idea of why this is happening, but just wanted to check with you guys.

So my two questions:

Why am I getting the second error? I know I changed to a user with fewer privileges, but the original_Login is still MyDomain\Bob.Smith.

How can I go back to my original login so that I can query Employees? The SUSER_SNAME() is 'TempUser'

Solution

  • In order to go back to your original user, you run:

    REVERT

    That is the only way. Until you run it, your original permissions do not matter, and TempUser does not have permission to impersonate Bob.