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: Wednesday, May 15, 2013 9:55 AM
Points: 2, Visits: 28
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: 2 days ago @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
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: Wednesday, May 15, 2013 9:55 AM
Points: 2, Visits: 28
thank you i solved this using ddl trigger
Post #1451745
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse