Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a requirement where I need to audit (and potentially be notified) whenever the 'sa' user account logs into an SQL Server 2005 instance. Additionally, there is a preference that the database that the login accesses is also auditted.

I understand that I could use extended events or server audit in a newer version of SQL Server, but as I am limited to SQL Server 2005, the only option I can think of is to:

  • set up a server-side trace for user logon events for the 'sa' user into a table
  • set up a job to email the contents of the table at a specified interval

Are there more efficient ways to accomplish this in 2005 ?

share|improve this question
2  
Have you looked at Event Notifications? Good starting article on SSC here: sqlservercentral.com/articles/Event+Notifications/68831 –  Shawn Melton Nov 8 '12 at 5:56
    
Event notifications also not a bad approach in SQL 2005 if you don't find what you need via logon triggers alone. –  Mike Walsh Nov 10 '12 at 3:46

1 Answer 1

up vote 2 down vote accepted

I would say consider logon triggers to accomplish this. This way you aren't using a server side trace. You can also audit all sucessful logins in the SQL Server error log (right click on instance, properties, security and then choose both successful and failed logons.. I don't believe this shows you the DB context info, though)

Also - I would seriously consider upgrading to a later version of SQL if possible. SQL 2005 is two (three if you count 2008 R2) versions back. I know you probably know that but I'd feel bad if I didn't call it out :-)

I would also highly discourage the use of SA for any logons. SA is a highly privileged account. It is -the- highly privileged account in SQL Server. Everyone knows there is an account named SA and it can be prone to hack attempts. I tend to push for windows authentication only and ensure a group is added that the proper DBAs team can properly and securely added to. I often disable the SA account in mixed mode and will create another account with SA rights but a non descript name. If, for some reason, the SA account needs to stay around, I try and give that a horrible password and store it someplace really safe and not use it.

By not having an SA account used by so many people that you need to audit its usage, you can give more granular permissions to do the required activities in SQL and no more. Least privilege and tight access lists will take you much further than watching SA account activity.

At any rate, Logon triggers may be the best bet. Trace would work but there is a cost associated with that (there is with logon triggers also but my guess is the cost of trace will be more expensive for you)

share|improve this answer
1  
Thanks Mike, I was ignorant of logon triggers. I agree with everything you have mentioned, but unfortunately don't have control of version upgrades or changing the 'sa' password, which is why I'm in the situation that this needs to be auditted. –  WT_W Nov 8 '12 at 5:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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