December 15, 2009 at 1:27 pm
I'm looking to create a trigger to audit permission rights given to users. Is there a particular event to look up in creating this particular situation. I have a trigger the records the account being create and by which user. I would like to capture if server permissions are granted to that account.
Thanks,
December 16, 2009 at 7:51 am
A DDL trigger at the server scope could do this. I think you'd want to look at these events:
.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 16, 2009 at 8:04 am
I forgot to mention you could also use a server-side trace or query the default trace for the Audit Add Login to Server Role Event. The only problem with this is that the Default Trace does not appear to audit "GRANT CONTROL SERVER TO login"
If you do your own server-side trace you would get the GRANT CONTROL SERVER command by tracing the Audit Server Scope GDR Event.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 16, 2009 at 8:48 am
A DDL trigger will grab things, but here's a problem. If you store this in the db, once the user has sysadmin, they can erase their tracks.
If you trigger on this, you need to ensure that multiple people are notified, or a note is made in a folder that the potential sysadmin cannot access. Typically a trace running for this specifically would log to a folder that sysadmins and domains admins do not have rights to access.
December 17, 2009 at 7:41 am
Jack/Steve,
Thanks for all your suggestions.
Jack
Question regarding the ADD_SERVER_ROLE_MEMBER.
"ADD_SERVER_ROLE_MEMBER" does not support synchronous trigger registration. Do you have any suggestion where I can look to resolve this issue. I'm not familiar the Service Broker.
December 17, 2009 at 8:11 am
Don't know why that is. Try checking out this article at MSSQLTips.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 10, 2010 at 5:15 am
Guys,
Can someone give me a script that fires a trigger whenever sysadmin access is granted to a perticular login.
Many thanks,
Boj
November 28, 2010 at 12:02 pm
yes
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply