November 10, 2006 at 1:56 pm
Hello,
Win2003/mssql2000 w/sp4
Is there a way to set/create a SQL Server Agent Alert that audits security changes to accounts and triggers an email to an operator similar to SQL Profiler Trace? I was hoping I could use the following Event conditions in an Alert:
Audit Add Login to Server Role Event
Audit Add Member to DB Role Event
Audit Add DB User Event
Audit Statement GDR Event
Audit Statement Permission Event
Audit Object GDR Event
I would like to avoid using SQL Profiler's Trace tool as it would need to run costantly on the server. Also, there is risk that someone/server shutdown may accidently close the active trace window, thereby stopping the trace.
Any other work-arounds/suggestions?
Many thanks. Jeff
November 10, 2006 at 2:38 pm
The answer is yes.
To trace login, use master..sysxlogins;
To trace users, use sysusers and sysprotect in each database;
Add triggers on these tables.
November 20, 2006 at 2:29 pm
Hi, I find that you can't add triggers to the system tables ... any other suggestions?
Many thanks. Jeff
November 21, 2006 at 9:59 am
That is correct. Triggers on system tables are not supported and not guaranteed to work.
You can use the trace stored procedures and functions. It's actually what Profiler does.
sp_trace*
fn_trace*
As far as SQL Agent alerts, if you execute the command that changes roles, adds a login, etc., it'll fire a message that's actually contained in sysmessages. Execute the command you want to trap for on a develoment server. Then search the description field of sysmessages for the message. That'll get you the "error" #.
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply