Sql-server – How to find out who’s logged on to database

sql serverssms

I'm trying to rename a database in SSMS and I get this error:

The database could not be exclusively locked to perform the operation

So how can I find out who or what is using this database? I don't want to go and change anything without knowing what is going to be effected.

Thanks

Best Answer

The script below shows what database has users connected, what username, and machine name attached.

SELECT @@ServerName AS SERVER
 ,NAME
 ,login_time
 ,last_batch
 ,getdate() AS DATE
 ,STATUS
 ,hostname
 ,program_name
 ,nt_username
 ,loginame
FROM sys.databases d
LEFT JOIN sysprocesses sp ON d.database_id = sp.dbid
WHERE database_id NOT BETWEEN 0   AND 4
 AND loginame IS NOT NULL 
 order by hostname

enter image description here