3

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?

2

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.

0

for the users that do not have a login in the server follow this link:

--technet article on orphaned users

Report on failed users - shows all SQL users on server

USE [<database>]
EXEC sp_change_users_login  'REPORT'

--SQL 2000 and earlier
USE [<database>]
EXEC sp_change_users_login  'Auto_Fix', 'MyUser'

--SQL 2005 onwards
USE [<database>]
ALTER USER [MyUser] WITH LOGIN = [MyUser]

--to find database users (windows accounts) without Server login
select users.name, logins.name
from master..syslogins logins 
    right join sysusers users on logins.sid = users.sid 
where logins.sid is null 
   and issqlrole <> 1 
   and isapprole <> 1   
   and (users.name <> 'INFORMATION_SCHEMA' 
    and users.name not in ('guest', 'dbo', 'sys', 'system_function_schema'))

a number of process running for a long time that are blocking and causing other issues

for this I recommend the procedure called sp_whoisactive

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.