April 15, 2014 at 9:51 am
Is there such a tool that will use the security setup from a SQL server, cross-referenced with Active Directory to make it easier to look at what's going on?
I walked into this job 6 months ago with a ton of users and groups already set up and configured based on many peoples' different ideas of how things should be. Sure enough, I have several users that have ended up with permissions they shouldn't have. Tracing the users and their groups and the groups that are in groups and then there's schemas... it's all harrowing and I'm about ready to rip my hair out. Does anyone know of anything?
April 15, 2014 at 11:43 am
I have created something with powershell that first pulls all of the users on both the server and the instance and put those into separate tables. Then I have another powershell script that grabs only the groups and unions the two lists together. From there you can use AD powershell commands (I think this is something you need to download from Microsoft) to list the members of each group and put that into a table.
I then created reports that shows who has access at a server and instance level that expands the groups into users. I also created an SSRS report that allows you to put in the users AD username and get all of the servers, instances, databases, and groups they have access to. We use this mainly for terminations so we know we have cleared up all the users access.
The one issue is nested groups. Unless you write some sort of recursive routine or get rid of nested groups altogether it is pretty difficult to grab all of these easily.
Hope that helps
- Tony Sweet
April 15, 2014 at 12:36 pm
The xp_logininfo stored procedure can be used to return the members of a domain group.
A couple years back I wrote a script that combines sys.server_principals and xp_logininfo to return individual domain accounts that have either been explicitly granted sysadmin membership or have inherited sysadmin via domain group membership.
http://www.sqlservercentral.com/articles/Security/76919/
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 16, 2014 at 7:12 am
Eric M Russell (4/15/2014)
The xp_logininfo stored procedure can be used to return the members of a domain group.A couple years back I wrote a script that combines sys.server_principals and xp_logininfo to return individual domain accounts that have either been explicitly granted sysadmin membership or have inherited sysadmin via domain group membership.
This seems so close to what I'm looking for. The issue isn't sysadmin though. The people in question don't have THAT big of permissions. They're just able to write and change the schema. They don't have the ability to do security stuff or anything. It's like they have dbo permissions on certain databases.
April 16, 2014 at 9:57 am
robin.pryor (4/16/2014)
Eric M Russell (4/15/2014)
The xp_logininfo stored procedure can be used to return the members of a domain group.A couple years back I wrote a script that combines sys.server_principals and xp_logininfo to return individual domain accounts that have either been explicitly granted sysadmin membership or have inherited sysadmin via domain group membership.
This seems so close to what I'm looking for. The issue isn't sysadmin though. The people in question don't have THAT big of permissions. They're just able to write and change the schema. They don't have the ability to do security stuff or anything. It's like they have dbo permissions on certain databases.
OK, that part of the existing script inserting @principals uses is_srvrolemember() function to identify principals (accounts and groups) with sysadmin role membership. There is another function called IS_MEMBER() which can be used to determine membership in any role, like 'db_owner'. You can retofit the script for your need by replacing reference to is_srvrolemember() with is_member(). There may be a couple of more changes, but that's basically it.
http://msdn.microsoft.com/en-us/library/ms186271.aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply