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

how to find out fixed server role changes to user? Expand / Collapse
Author
Message
Posted Friday, May 10, 2013 1:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 9:04 AM
Points: 2, Visits: 31
i want to track all the fixed roles changes in sql server how do i go about doing that
for example if i have users and privileges

user fixed server roles
john db_owner
doe bulkadmin

if user user permission john is changed to bulk admin i want an alert how do i go about doing this in sql server
Post #1451724
Posted Friday, May 10, 2013 1:51 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:21 AM
Points: 7,135, Visits: 12,749
A Database DDL Trigger for the events ADD_ROLE_MEMBER or DROP_ROLE_MEMBER might give you the oversight you want. Note that a member of db_owner or sysadmin can always circumvent the triggers by disabling and enabling them or dropping and re-adding them before and after they make changes.

Another option that could go hand-in-hand with a DDL Trigger or be a stand-alone solution is to take snapshots of the role members periodically and compare it to the last snapshot to see if anything has changed. This will not tell you who made the change though, just that a change was made.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1451734
Posted Friday, May 10, 2013 3:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 9:04 AM
Points: 2, Visits: 31
thank you i solved this using ddl trigger
Post #1451745
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse