3

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
Improve this question
6
  • What database are you using? Also, post whatever code you were using before it got messed up.
    – LowlyDBA - John M
    Dec 2, 2014 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 2, 2014 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 2, 2014 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 2, 2014 at 19:57
  • Do you have a backup of msdb from before the "cleaning"?
    – Sebastian Meine
    Dec 2, 2014 at 20:04

1 Answer 1

Reset to default
1

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.