Learn SQL Server User Impersonation

As discussed in earlier Login Impersonation Blog 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.

Database 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 User else you’ll not be allowed to impersonate and you’ll face the similar error mentioned below:

Impersonate error:

Msg 15517, Level 16, State 1, Line 1
Cannot execute as the database principal because the principal “Sarab_WriterRole” does not exist,
this type of principal cannot be impersonated, or you do not have permission.

 

Check the User Permission to Impersonate

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

select * from sys.Database_principals

If the current logged in user 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_WriterRole, it is now confirmed that Sarabpreet login doesn’t have appropriate impersonate permissions.

 

Grant Impersonate Permission on User

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 user Sarabpreet can impersonate as Sarab_WriterRole.

GRANT IMPERSONATE ON User::Sarab_WriterRole TO Sarabpreet

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

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

To Revoke Impersonate Permission from User

REVOKE IMPERSONATE ON User::Sarab_WriterRole TO sarabpreet

 

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