Help! How can I audit logins in SQL Server?
-
Is it possible to audit logins WITHOUT doing a TRACE on a SQL Server database?
I've looked at the SP_WHO and SP_WHO2 stored procs and found that at any given moment, logged on user information is sitting in a table in the master database named sysprocesses.
This made me think: Can I create a TRIGGER on the sysprocesses table in master, that will allow me to write an audit log of logins. I tried this (logged on as SA) and SQL Server told me that I cannot create a TRIGGER on the sysprocesses table.
One way of doing this, which I wouldn't particularly want to do, because it isn't the ideal way, is to write a daemon that checks every few seconds who is/has logged on since the last check, and report on it.
Is there another better way that will take less system resources? Maybe even the TRIGGER idea?
Any help on this one will be greatly appreciated,
Steve
-
You can audit logins in SQL server. You have the option of auditing None, Failure, Success or ALL. Have a look in BOL as it gives details. The next level is C2 auditing but this may be more that you want. To see the options, right click on your server in enterprise manager and select properties and click the security tab.
Hope this help
Andrew
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply