December 3, 2003 at 3:08 pm
I need to set up audits for adding and deleting logins. I added two alerts on the server for 15298 New Login Created and 15479 Login Dropped and have them log event to the Application event log.
So, the first thing I notice is that they aren't logged when I add/delete logins from EM, only when I use sp_addlogin/sp_droplogin in QA. Can this be??!? Is there another way to audit these? Also, I set up the alerts to NET SEND me as well, and this only happens when I use QA. Are there other messages that get generated when I use EM, which I presume uses SQLDMO to do the dirty work?
aaron chavez
-------
at us, very deafly, a most stares
collosal hoax of clocks and calendars
eecummings
-------
at us, very deafly, a most stares
collosal hoax of clocks and calendars
eecummings
December 3, 2003 at 3:19 pm
Both of those IDs are raised using RAISERROR() in the appropriate stored procedures. EM uses sp_addlogin and sp_droplogin, at least according to a Profiler trace I just ran. You didn't, per chance, add and remove Windows logins through EM and regular SQL Server logins through QA, did you? If so, Windows accounts have different event IDs...
15481 >> sp_grantlogin
15485 >> sp_revokelogin
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
December 3, 2003 at 3:40 pm
Worked...
OK, so I added the other alerts and observed the following:
EM -> delete domain login = 15485
EM -> add domain login = 15481
QA -> drop domain login (domain\loginID) with sp_droplogin = 15479
QA -> add domain login (domain\loginID) with sp_addlogin = 15298
Now I gotta get unicenter to scan the logs, oh joy.
Thanks!
-------
at us, very deafly, a most stares
collosal hoax of clocks and calendars
eecummings
-------
at us, very deafly, a most stares
collosal hoax of clocks and calendars
eecummings
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply