Need to be notified when certain users log in 1

[tadd]

I'm a newbie to security, alerts, notifications...

I need to be notified when certain users login to my database. I have changed the Audit Level to "All" so that SQL keeps a log of when someone logs in. Can I somehow assign alerts to these login events? Or is there a way to query the SQL Server Logs and filter out the events I am looking for?

Thanks for any direction...

 

[ptheriault]

what version of SQL Server?

- Paul
- Database performance looks fine, it must be the Network!

 

[tadd]

I am using SQL 2000

 

[ptheriault]

In SQL 2000 you can create an alert that will send you an email based on the error number written to the application log. However I don't know of anyway to create an alert based on the login name. You can create a trace file and look at the login history.

The bottom of this page will explain how to create that trace.

http://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx

- Paul
- Database performance looks fine, it must be the Network!

 

[tadd]

Thanks Paul.

The article states that "Unlike C2 auditing, traces aren't persistent." This is a problem because I need something that will persist when the server is restarted. On the other hand, C2 auditing is *way* more than I need here and I don't want to take the performance hit from C2.

I saw another thread that I thought would solve my problem, (thread962-1092924) but I am confused by what is being said there.

If there is a way to query the SQL Server Log using Transact-SQL, then I suppose I could search for specific login records and maybe email them out when found. But it looks like the SQL Server Log is stored in text files?

 

[ptheriault]

you can't query the sql server log. They are not stored in text format. You need a 3rd party tool like Log Explorer by lumigent. they also make an audit tool. You could check red gate software too. I've never looked at it so i'm not sure how good it is. But it might be easier than trying to re-invent something already done.


- Paul
- Database performance looks fine, it must be the Network!

 

[tadd]

Having tinkered with this a bit, I seem to have found an easy solution.

I can create an Alert that will send me an email when the log message contains certain text. So, I can configure the alert to fire when the error text contains the following phrase:

Login succeeded for user 'MYCORP\John.Doe'.

I have tested this and it works.

If I need to track other specific logon messages, I can just create more Alerts.

-----

Now....my problem is that my supervisor just told me that what he really wants is to know when any user EXCEPT Jane Doe logs on.....Hmmm....back to the drawing board....