Trigger for sysadmin grants

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Security » Trigger for sysadmin grants

Trigger for sysadmin grants

Rate Topic

Display Mode

Topic Options

Author

Message

Ryan D.

Posted Tuesday, December 15, 2009 1:27 PM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 6:10 AM
Points: 92, Visits: 1,271

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

Jack Corbett

Posted Wednesday, December 16, 2009 7:51 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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

Jack Corbett

Posted Wednesday, December 16, 2009 8:04 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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.

Post #835110

Steve Jones - SSC Editor

Posted Wednesday, December 16, 2009 8:48 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 1:14 AM
Points: 31,433, Visits: 13,746

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

Ryan D.

Posted Thursday, December 17, 2009 7:41 AM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 6:10 AM
Points: 92, Visits: 1,271

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

Jack Corbett

Posted Thursday, December 17, 2009 8:11 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

Don't know why that is. Try checking out this article at MSSQLTips.

Post #835702

Mithra

Posted Tuesday, August 10, 2010 5:15 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, March 07, 2013 10:08 AM
Points: 31, Visits: 367

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

myplantldy

Posted Sunday, November 28, 2010 12:02 PM

Forum 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 »

Permissions