May 23, 2012 at 8:22 am
Good morning everyone, ran into somewhat of an odd question and thought i'd run it through the community:
Background:
sql 2005 and 2008 r2 environment, mix of both.
Logins for all instances are always groups, example adventureworks_dbo (has dbo permissions), adventureworks_readonly, etc, etc
I keep running into the issue of users getting permissions they shouldn't do to being added to groups. For example adventureworks_dbo is a group in active directory, but instead of simply having users in there it also has other groups, thus groups within groups, within groups (think of the movie Inception, that is the environment of permissions....)
So someone who is not a DBA adds john smith to a group, but it so happens that group is also in adventureworks_dbo, so mr john smith without knowing it gets dbo access to a production database.
How can I audit something of this nature? If it was just users and not groups I could use policy based management to lend a hand, but being that it's only active directory groups i'm at a lose for options?
And thoughts are appreciated, thanks again.
May 23, 2012 at 8:30 am
there is no way to audit people adding people to groups as thats outside of SQL, what you could do is run xp_logininfo on the group at regular times and see what has changed one day from the next. might get tricky with groups within groups. the other option would be some 3rd party AD monitoring software which logs changes to AD objects and sends you email. (could if possible, use powershell to do it)
May 23, 2012 at 8:35 am
We do use quest change auditor for active directory to monitor changes there, and I know they have a sql addon for this product also. I was hoping that something already existed to solve this issue rather inside of sql rather than purchase another 3rd party product
May 23, 2012 at 8:45 am
change auditer, does log the changes to a SQL DB (if its configured) so you could do a query against its DB to see whats changed
May 24, 2012 at 6:21 pm
jdillon 62969 (5/23/2012)
...Logins for all instances are always groups, example adventureworks_dbo (has dbo permissions), adventureworks_readonly, etc, etc
I keep running into the issue of users getting permissions they shouldn't do to being added to groups. For example adventureworks_dbo is a group in active directory, but instead of simply having users in there it also has other groups, thus groups within groups, within groups (think of the movie Inception, that is the environment of permissions...
Well, it does seem like a nightmare ... 😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply