Learn SQL Server Login Impersonation

Technically SQL Server and windows both can be configured to authenticate to another SQL instance\Windows Server against the current login account, this behavior is known as Impersonation. When one login\user acts on behalf of another login\user this behavior is also known as impersonation.

Server Level

We can easily impersonate a session by explicitly mentioning a standalone EXECUTE AS statement but for doing so you should have Impersonate permissions on the other login else you’ll not be allowed to impersonate and you’ll face the similar error mentioned below:

Impersonate error:

Error: Msg 15406, Level 16, State 1, Line 1

Cannot execute as the server principal because the principal “sarab_SecurityAdmin” does not exist, this type of principal cannot be impersonated, or you do not have permission.

 

Check the Permission to Impersonate

Let’s check if the login (Sarabpreet) has the Impersonate Permission to impersonate as Sarab_SecurityAdmin, for doing so we’ll run the below mentioned query:

select * from sys.server_principals

If the current logged in login has impersonate permission to impersonate the principal name will appear in the result set. Since we got the error & now the result set also doesn’t show the principal Sarab_SecurityAdmin, it is now confirmed that Sarabpreet login doesn’t have appropriate impersonate permissions.

 

Grant Impersonate Permission on Login

To grant the impersonate permission you can execute the below mentioned query, in this query we are granting Impersonate permission to Sarabpreet so that the login Sarabpreet can impersonate as Sarab_SecurityAdmin.

GRANT IMPERSONATE ON Login::Sarab_SecurityAdmin TO Sarabpreet

Since we’ve granted the permission let’s cross verify this once:

You can see that now the principal Sarab_SecurityAdmin is visible to Sarabpreet login, which means now the login Sarabpreet can execute queries by impersonating as Sarab_SecurityAdmin.

 

To Revoke Impersonate Permission

REVOKE IMPERSONATE ON Login::Sarab_SecurityAdmin TO sarabpreet

For User Impersonation read: Learn SQL Server User Impersonation

 

Hope you enjoyed the post, feel free to leave a comment. 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox
Follow @SQLDB