0

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.

1

All you need to do is JOIN to sysusers

SELECT  p.spid, p.dbid, p.uid, u.name
FROM    sys.sysprocesses p
JOIN    sys.sysusers u ON p.uid = u.uid
  • Thanks James but it does not give the correct user name for queries running on different databases. i.e. if you run the above query in context of master database and there are some other queries running on some other DBs, u.name gives incorrect results. – SQLPRODDBA Feb 1 '16 at 9:07
1

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.

  • Thanks Daniel but it does not give the correct user name for queries running on different databases. i.e. if you run the above query in context of master database and there are some other queries running on some other DBs, dp.name gives incorrect results. Because above query runs in context of master database and has only details of master..database_prinicipals where in reality it should get those details from respective DBs of database_prinicipals.. – SQLPRODDBA Feb 1 '16 at 9:11
  • That's correct. The sys.database_principals view is local for the current database. You can prefix the view name with the name of the database to run it in a specific database. For instance ... LEFT JOIN mydatabase.sys.database_principals. – Daniel Hutmacher Feb 1 '16 at 9:15
  • So basically I was looking for a way to get this in simple query with sysprocesses and without having to go to individual databases. Is that possible? Currently what I am doing is extracting all users for all DBs in temp table first and then joining that with sysprocesses. – SQLPRODDBA Feb 1 '16 at 9:24
  • Nope. You're going to have to loop through the databases and collect the user names. I would use a temp table to collect all of the users in a single table, then build your query using that table. – Daniel Hutmacher Feb 1 '16 at 9:32
  • Yup. I am doing the same currently. – SQLPRODDBA Feb 1 '16 at 10:12

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.