Sql-server – user/login name on process unknown

loginssql serversql-server-2012windows-server

SQL Server version is 11.0.5569.0.

The user/login for the process on a server is as follows:

S-1-9-3-412646706-1242810017-220819094-3312857753

This is not an orphaned user (as suggested below) because I checked that before entering this post.

I looked at the items running using Ideradm, sp_blitzfirst, and sp_whoisactive. The explain plan indicates that this is working with a couple of temp tables that it obviously created in a prior step.

When I ran a script against the 'principals' tables, NADA, nothing.

Any other suggestions?

Best Answer

EXECUTE AS USER = 'SomeUser'; where 'SomeUser' is a user who doesn't have a linked login at the server level will result in SQL Server reporting the login name as a SID starting with s-1-9-3.

You can use the original_login_name column in sys.dm_exec_sessions to determine who or what is impersonating the user without a login; for instance:

SELECT SessionID = des.session_id
    , LoginName = des.login_name
    , OriginalLoginName = des.original_login_name
FROM sys.dm_exec_sessions des
WHERE des.is_user_process = 1;

I wrote a blog-post over at SQL Server Science showing this behavior, and how to identify which database-level-principal is being impersonated.

Related Question