How to find All user ids who use my test database and to terminate their running jobs?

Question

Hello,
I would like to get your help and expertise on how to find all user id who use the database because I want to terminate their jobs after after certain time at night, let's say 10pm at night when I want to users using the database because I have a batch job running at 10pm and I want to use the computer resources for the batch job.

I googled and I found the following sql statements for terminating all session id that use my AdventureWorksDw2019 database

use master

DECLARE @kill varchar(8000) = '';  
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  
FROM sys.dm_exec_sessions
WHERE database_id  = db_id('AdventureWorksDW2019')

I would like to know the user ids of all of the session ids that I terminate above. Do you know if there is a table that links with this sys.dm_exec_sessions that may give me all of the user ids ?

I hope that my question makes sense. If it is not, may you please propose a way that could accomplish the same goal?

Many Thanks for the help,
Du

Answer 1

Not sure what your question really is. The name of the login is in the column sys.dm_exec_sessions.login_name.

But a more direct way is to run

ALTER DATABASE yourdb SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

This will kick out all non-admin users. You need this command at the end of the job:

ALTER DATABASE yourdb SET MULTI_USER

Then again, as Tom suggests, this nothing you do normally. Least of all because you "want to use the computer resources for the batch job." But some applications have nightly batches where bad things could happen if users area active in the system when the nightly batch runs. But this is something you typically solve in the application, one way or another.

Answer 2

Check if this query returns all the information which you need:

select *
from sys.dm_exec_requests ER
left join sys.dm_exec_sessions ES on ES.session_id = ER.session_id
left join sys.dm_exec_connections EC on EC.session_id = ER.session_id
order by ER.session_id

Between all you can get the original_login_name, original_security_id, nt_domain, nt_user_name, security_id

Answer 3

Thank you for the help. I ran the query and it gave me all information including the database_id and user_id which I am interested in. Since I am new , are the database_id and user_id always in integer value because it gave me 1 for database_id and 1 for user_id. In my situation, my user id is n32456 then could I ask why n32456 not show up?

Also If I want see all user_id who log into my database AdventureWorksDW2019 then how do I include it in the query?

Many Thanks,
Du

Answer 4

Hi @minh tran ,

Please try below T-SQL;

1. Find database id; select * from sys.databases where name='test'

2... Find all user id who use the database.

Select session_id, login_name, database_id from sys.dm_exec_sessions where database_id=8  

3. kill all connections to this database

USE [master];  
  
DECLARE @kill varchar(8000) = '';    
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'    
FROM sys.dm_exec_sessions  
WHERE database_id  = 8  
  
EXEC(@kill);  

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 5

gave me 1 for database_id and 1 for user_id

User ID 1 is the "sa" SysAdmin account and Database ID 1 is the system database "master".
Be carefull with killing process which you simply don't know.