Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trigger for sysadmin grants Expand / Collapse
Author
Message
Posted Tuesday, December 15, 2009 1:27 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 1:32 PM
Points: 94, Visits: 1,521
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,
Post #834738
Posted Wednesday, December 16, 2009 7:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 10:57 AM
Points: 10,387, Visits: 13,454
A DDL trigger at the server scope could do this. I think you'd want to look at these events:

  • ALTER_AUTHORIZATION_SERVER

  • ADD_SERVER_ROLE_MEMBER

  • ADD_SERVER_ROLE_MEMBER - you'd want this one so you can see if there is someone adding then quickly dropping the permissions
  • .
  • GRANT_SERVER






Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #835094
Posted Wednesday, December 16, 2009 8:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 10:57 AM
Points: 10,387, Visits: 13,454
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #835110
Posted Wednesday, December 16, 2009 8:48 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Saturday, December 20, 2014 3:11 PM
Points: 31,368, Visits: 15,837
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #835165
Posted Thursday, December 17, 2009 7:41 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 1:32 PM
Points: 94, Visits: 1,521
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.
Post #835678
Posted Thursday, December 17, 2009 8:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 10:57 AM
Points: 10,387, Visits: 13,454
Don't know why that is. Try checking out this article at MSSQLTips.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #835702
Posted Tuesday, August 10, 2010 5:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 3:45 PM
Points: 31, Visits: 383
Guys,

Can someone give me a script that fires a trigger whenever sysadmin access is granted to a perticular login.
Many thanks,
Boj
Post #966555
Posted Sunday, November 28, 2010 12:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, November 28, 2010 12:02 PM
Points: 1, Visits: 0
yes
Post #1027085
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse