I have users that have access to several databases. I used to have an alert and notification sent to me when a specific user connects to a specific database, but I accidentally clobbered it. Now I have to recreate it.
If I understand correctly, logon triggers won't help, since I don't need to know when they connect to other databases.
Please help! Thank you!
There are three ways to find who is connected to a database in sqlserver.
First one:
Use the SQL SERVER Management Studio -- Management -- Activity Monitor This gives a list of users, database names, status, command, transactions, application with which they are using the database, CPU, IO and Memory usage, Login time etc.
Second One:
Use the built in stored procedure called sp_who2 Run the command exec sp_who2 This gives a list of users, database names, status, command, program with which they are using the database, CPU time, Login time etc. Third One:
Third one Use the script
DECLARE @temp TABLE(spid int , ecid int, status varchar(50),
loginname varchar(50),
hostname varchar(50),
blk varchar(50), dbname varchar(50), cmd varchar(50), request_id int)
INSERT INTO @temp
EXEC sp_who
SELECT COUNT(*) FROM @temp WHERE dbname = 'DB NAME'
From the above methods, you can use them to implement in a sql job to get required notification.
The best method i used is the latest sp_whoisactive by MVP Adam Machanic
