As either a DBA or DBD, undoubtedly, you’ve needed to create a user account with very specific permissions. Especially when that account is based on Windows authentication, it can be particularly difficult to test and verify the correct permissions were assigned. However, if you have the access – say, sysadmin – you can simply impersonate the account yourself and verify everything is set correctly. Here’s how:

--display the current user we're accessing as
SELECT suser_name(), original_login() --DOMAIN\YourAccount, DOMAIN\YourAccount
--here's the actual impersonation. this can just as easily be a normal <abbr title="Structured Query Language">SQL</abbr> account
EXECUTE AS USER = 'DOMAIN\AnotherAccount'
--display the current user we're accessing as. note that SUSER_NAME() is the account specified above
SELECT suser_name(), original_login() --DOMAIN\AnotherAccount, DOMAIN\YourAccount
/*
run test cases here to verify correct settings
*/
--this steps us back out to the normal context
revert
--verify that we've reverted back to ourselves
SELECT suser_name(), original_login() --DOMAIN\YourAccount, DOMAIN\YourAccount

SUSER_NAME() returns the login identification name of the user. ORIGINAL_LOGIN() shows the login of the user actualyl connected to SQL Server. REVERT repeals the context back a level. Note, that if you EXECUTE AS multiple times, you’ll have to issue a REVERT for each level. That’s because when you EXECUTE AS within an impersonation, you’re actually doing it as the impersonated account (doesn’t that seem obvious?). Of course, you’ll only be able to EXECUTE AS if that impersonated account has the correct access.

What happens if you try to impersonate an account in a database where they don’t have access?

Msg 916, Level 14, State 1, Line 1
The server principal "DOMAIN\AnotherAccount" is not able to access the database "TestingDB" under the current security context.

A quick note on security groups: If you’re testing access against a security group, you must impersonate a user within that group. If you try and EXECUTE AS USER = ‘DOMAIN\GroupName’, you will receive the following error:

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

EXECUTE AS and REVERT are great tools for troubleshooting permissions errors whether they be from a user receiving an error or application login that needs to be locked down tight.