Sql-server – Find database user name from sysprocesses

scriptingsql server

I know that this is a strange requirement but I need to find out database user name(not the login name) from sys.sysprocesses for all the currently running processes. For e.g. select spid,dbid, uid from sysprocesses where uid corresponds to database user in that particular database. So what I want is one more column which can get me database user name from uid. Please let me know if this is possible with just a simple select query.

Best Answer

Logins (on the server) do not automatically correspond to users (in the database). For instance, "sa" does not have its own user in the database, but uses "dbo". This might get you started.

SELECT s.session_id,
       sp.[type], sp.type_desc,
       sp.[name] AS loginName, dp.[name] AS userName,
       sp.is_disabled
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.database_principals AS dp ON s.security_id=dp.[sid]
LEFT JOIN sys.server_principals AS sp ON s.security_id=sp.[sid];

Note: sysprocesses, sysusers, etc are deprecated and will probably stop working in a future version of SQL Server.

I go into a bit more detail on SQL Server security in my blog post.

Related Solutions

SQL Server Login Mapping – How to Map a SQL Server Login to an Existing User in a Read-Only Database

Since the status of the database is Standby, I'm assuming this database is a Log Shipping secondary. If that's the case, the database cannot be written to at all on the secondary (making it writable would break Log Shipping).

Also, since you said that other databases (presumably not read-only) have correct mappings, I'm assuming you're using a SQL login. If that's the case, all you should have to do is recreate the login on the secondary server using CREATE LOGIN ... WITH SID to match the SID from the primary (look in sys.server_principals on the primary server to find it).

On the other hand, if you're using Active Directory for the login, you'll have to add the user principal to the primary database so that when it comes across to the secondary in a log backup, there's no need to do any remapping (note: depending on the situation, this may be a lot easier said than done!).

SQL Server – How to Get Information About Sleeping Processes Causing Blocking

This is what I use to monitor sleeping SPIDs causing blocking issues:

SELECT 
     s.session_id
    ,s.status
    ,s.login_time
    ,s.host_name
    ,s.program_name
    ,s.host_process_id
    ,s.original_login_name
    ,s.last_request_end_time
    ,CAST(t.text AS nvarchar(4000)) AS [text]
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_connections AS c
    ON s.session_id = c.session_id
CROSS APPLY (
    SELECT MAX(DB_NAME(dt.database_id)) AS database_name
    FROM sys.dm_tran_session_transactions AS st
    INNER JOIN sys.dm_tran_database_transactions AS dt
        ON st.transaction_id = dt.transaction_id
    WHERE is_user_transaction = 1
    GROUP BY st.session_id
    HAVING s.session_id = st.session_id 
) AS trans
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS t
WHERE s.session_id NOT IN (
        SELECT session_id
        FROM sys.dm_exec_requests
    )
    AND s.session_id IN (
        SELECT request_session_id
        FROM sys.dm_tran_locks
        WHERE request_status = 'GRANT'
    )
    AND STATUS = 'sleeping'
    AND is_user_process = 1;

Hope this helps.

To answer your question specifically, sys.dm_tran_database_transactions holds the information you're looking for.

Best Answer

Related Solutions

SQL Server Login Mapping – How to Map a SQL Server Login to an Existing User in a Read-Only Database

SQL Server – How to Get Information About Sleeping Processes Causing Blocking

Related Question