How can I create notification on user's connecting to database?

Question

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

What database are you using? Also, post whatever code you were using before it got messed up. — LowlyDBA - John M, Dec 02 '14 at 16:09
My apologies, it is on MS SQL 2005. Unfortunately, I don't have the old code. In my attempt to "clean up" after previous DBAs, I wiped out several hundred maintenance jobs and alerts that have not worked in years. Apparently, one of them was useful — Sergey, Dec 02 '14 at 16:31
Are you able to set the login's default database for the specific scenario when you need to be alerted? Are there stored procedures or changes being done that you could attach the trigger to instead? — LowlyDBA - John M, Dec 02 '14 at 16:45
hmm, I was able to set up alerts to notify on "error 18454", which is successful logon. However, this alert fires only if I do not specify which database to watch. I assume, it just looks at the windows log, which only stores logins rather than database connections. — Sergey, Dec 02 '14 at 19:57
Was it maybe as simple as running an SP_WHO2 every few minutes and checking if that user had a connection to DBs? Or maybe it was using {SELECT db_name(dbid) as theDB FROM sys.sysprocesses WHERE loginame in ('aaa','bbb') and db_name(dbid) in ('yyy','zzz');}? Not pretty, but might be what was going on. — Martin S. Stoller, Dec 03 '14 at 00:41

score 1 · Answer 1 · edited Dec 08 '14 at 17:42

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

How can I create notification on user's connecting to database?

1 Answers1