EXECUTE AS LOGIN vs EXECUTE AS USER: Where Impersonation Stops

EXECUTE AS LOGIN and EXECUTE AS USER look like twins, but they stop in different places. A login is a server-level identity that can travel between databases. A user belongs to one database and stays inside it. Know which one you are testing, and always REVERT when you are done.

Why this trips people up

A junior DBA gets a ticket: “The report works for me but fails for the sales team.” They reach for impersonation, run EXECUTE AS USER, and everything works. Then the sales team still gets an error. The test used the wrong kind of identity, so it proved nothing.

The demo creates two small databases, SqlAuthorityDemo and SqlAuthorityOther. It also creates one server-level object, a login named DemoLogin, and removes everything at the end. The login gets a random password nobody sees, since we only impersonate it.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
DROP DATABASE IF EXISTS SqlAuthorityOther;
IF SUSER_ID(N'DemoLogin') IS NOT NULL DROP LOGIN DemoLogin;
GO
CREATE DATABASE SqlAuthorityDemo;
CREATE DATABASE SqlAuthorityOther;

DECLARE @Pwd nvarchar(50) = CONVERT(nvarchar(50), NEWID());
EXEC (N'CREATE LOGIN DemoLogin WITH PASSWORD = N''' + @Pwd + N''', CHECK_POLICY = OFF;');

Next, a table in the other database, and two users in the demo database. DemoUser is mapped to the login. ScopedUser has no login at all.

USE SqlAuthorityOther;
CREATE TABLE dbo.Secret (Id int, Note varchar(20));
INSERT dbo.Secret VALUES (1, 'other database');
GO
USE SqlAuthorityDemo;
CREATE USER DemoUser FOR LOGIN DemoLogin;
CREATE USER ScopedUser WITHOUT LOGIN;

Who are you now

Each step below switches identity, asks SUSER_SNAME for the login and USER_NAME for the database user, and then returns.

EXECUTE AS LOGIN = 'DemoLogin';
SELECT 'as login' AS step, SUSER_SNAME() AS login_name, USER_NAME() AS db_user;
REVERT;

EXECUTE AS USER = 'ScopedUser';
SELECT 'as user without login' AS step,
       CASE WHEN SUSER_SNAME() LIKE N'S-1-%' THEN N'(SID only)' ELSE SUSER_SNAME() END AS login_name,
       USER_NAME() AS db_user;
REVERT;

EXECUTE AS USER = 'DemoUser';
SELECT 'as mapped user' AS step, SUSER_SNAME() AS login_name, USER_NAME() AS db_user;
REVERT;

As the login, you are DemoLogin and the database user is DemoUser. As ScopedUser there is no login name, only a SID, which the query hides. As DemoUser, the login behind it is DemoLogin. So the user context carries the login name when a login exists.

Where the user stops

Now reach into the other database. DemoLogin has no user there yet, so this attempt should fail.

EXECUTE AS LOGIN = 'DemoLogin';
BEGIN TRY
    SELECT * FROM SqlAuthorityOther.dbo.Secret;
END TRY
BEGIN CATCH
    SELECT 'login, not mapped there' AS step, ERROR_NUMBER() AS error_number, ERROR_MESSAGE() AS message;
END CATCH;
REVERT;

Error 916 says the server principal DemoLogin is not able to access the database under the current security context. That is the usual answer when a login has no user in a database. Now map the login there, grant it the table, and try both kinds of impersonation again.

USE SqlAuthorityOther;
CREATE USER DemoUserOther FOR LOGIN DemoLogin;
GRANT SELECT ON dbo.Secret TO DemoUserOther;
GO
USE SqlAuthorityDemo;
EXECUTE AS LOGIN = 'DemoLogin';
SELECT 'as login' AS step, Id, Note FROM SqlAuthorityOther.dbo.Secret;
REVERT;

EXECUTE AS USER = 'DemoUser';
BEGIN TRY
    SELECT 'as user' AS step, Id, Note FROM SqlAuthorityOther.dbo.Secret;
END TRY
BEGIN CATCH
    SELECT 'as user' AS step, ERROR_NUMBER() AS error_number, ERROR_MESSAGE() AS message;
END CATCH;
REVERT;

The login version returns the row. The user version fails with 916 again, even though the same login is mapped in both databases. That is where impersonation stops. A user context lives inside its own database and does not carry across to another one. So if your real application reads across databases, test with EXECUTE AS LOGIN.

Do not forget the return trip

Each EXECUTE AS adds a layer, and each REVERT removes only one. The block below stacks two. After one REVERT you are still impersonating.

DECLARE @Me sysname = SUSER_SNAME();

EXECUTE AS LOGIN = 'DemoLogin';
EXECUTE AS USER = 'DemoUser';
REVERT;
SELECT 'after one REVERT' AS step, CASE WHEN SUSER_SNAME() = @Me THEN 1 ELSE 0 END AS back_to_original, USER_NAME() AS db_user;
REVERT;
SELECT 'after two REVERTs' AS step, CASE WHEN SUSER_SNAME() = @Me THEN 1 ELSE 0 END AS back_to_original, USER_NAME() AS db_user;

After the first REVERT, back_to_original is 0 and the user is still DemoUser. After the second, it is 1 and you are dbo again. A forgotten layer makes your next diagnostic fail for reasons that look unrelated. Always finish by checking who you are. The last block removes the demo, including the login.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
DROP DATABASE IF EXISTS SqlAuthorityOther;
DROP LOGIN DemoLogin;

One more rule. Do not widen trust settings or permissions just to make a test pass. Fix the real mapping instead.

Next time a user says it fails for them, test as the right kind of identity.

An impersonated context is not the original connection, it is a scoped identity needing a return.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.