Sql-server – How to create notification on user’s connecting to database

alertsconnectionsSecuritysql-server-2005

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!

  • Sergey

Best Answer

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

Related Question