1

Msg 15199, Level 16, State 1, Line 29 T he current security context cannot be reverted.

Please switch to the original database where Execute As was called and try it again.

execute as login='juno'
REVERT


DECLARE @User VARCHAR(20)
SELECT @USER = SUBSTRING(SUSER_SNAME(), CHARINDEX('\', SUSER_SNAME()) + 1, LEN(SUSER_SNAME()))
SELECT  [THE_SERVER]= @@SERVERNAME
        ,[DB_NAME] =DB_NAME()
        ,[@USER]=@USER 
        ,[SUSER_SNAME()]=SUSER_SNAME()
        ,[SYSTEM_USER]=SYSTEM_USER
        ,[USER_NAME()]=USER_NAME() 
        ,[CURRENT_USER]=CURRENT_USER
        ,[ORIGINAL_LOGIN()]=ORIGINAL_LOGIN()
        ,[USER]=USER
        ,[SESSION_USER]=SESSION_USER

enter image description here

What if I can't remember the original database I was when I executed the execute as login?

Is there a way to find out where I was?

1

There is no way to find it out, but you can try all the databases accessible by that login, i.e. under impersonated login execute the following code:

select 'use ' + name + '; revert;'
from sys.databases
where HAS_DBACCESS(name) = 1;

This way you find all the databases from which you could impersonate this login. Then you copy the output to query window and try it row by row until success.

But the easiest way to return yourself is just to change connection (right click in query window)

enter image description here

  • 1
    Note that query only works if the impersonated identity has view any database permission. – David Browne - Microsoft Oct 9 '18 at 14:15
  • 1
    @David Browne - Microsoft Any newly created login BY DEFAULT has VIEW ANY DATABASE permission as inherited from public fixed server role. If for any reason someone revoke/deny this DEFAULT permission he should write about it, shouldn't he? – sepupic Oct 9 '18 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.