SQL Authentication Via AD Groups Part II: Who has what access?
-
Comments posted to this topic are about the item SQL Authentication Via AD Groups Part II: Who has what access?
-
Wow! Thank you for this! As the "New" DBA at my company (aka the only one), noone has any idea what most databases are for or who uses them, and security auditing was on my list. After a couple test runs on some non-production databases, I'm not dreading it nearly as much as I was
-
This method doesn't handle nested AD groups does it?
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
I don't spend much time worrying about people in wrong groups other than any granted sysadmin or similar. If the company has groups set up so that managing them can be offloaded to someone outside the DBA team I'm going to trust them to get it right - I'm not the reviewer, approver, or safety net. Tough love! For all that it's interesting to see a daily report of changes that affect db access.
Where something like this helps is figuring out if/why/how they have access, or how to grant them the access. I typically have some "DB-" groups where I can manage access directly, but often those have nested groups (typically team groups) so that when a team member is added they get access automatically. It can in some environments be a pain to figure out the right place to add them.
Good follow up article.
-
KenpoDBA (2/18/2016)
This method doesn't handle nested AD groups does it?It does show anyone whose access comes in via a nested group. Are you looking for the entire group path back to the login?
If yes then not. I don't know if SQL server can resolve that.
Thanks
John.
-
Great method! I prefer not to use sp_msforeachdb as it can not always produce the right results. I don't remember the conditions, but I have had issues with it in the past in terms of it not hitting every database or even deadlocking. I prefer to use a cursor that can loop through databases that are in the proper state. Also, since sp_msforeachdb is undocumented, who knows what changes they may make to it in the future.
Jared
CE - Microsoft -
Yeah, I make a product that does this and also expands all the subgroups as well to give you not only a flat list, but also the full group chain from AD.
However, I go outside of SQL to do it and the confines of this article are to keep within SQL itself.
I've never been happy with xp_logininfo. I think it's a half-assed attempt to give us real info. A lot of things they write are like that though. They stop short of giving us what we actually need.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Excellent! Thank you for this handy tool.
-
I did some more experimenting with this, and noted one omission that might be useful - including "is_disabled' in the output. As it doesn't appear to come back when bringing in the Windows users, it might have to be a separate step in the job to update the temp table - like right after the Server_Roles section. I'll be trying to add it myself when I get a chance
-
Joe O'Connor (2/18/2016)
I did some more experimenting with this, and noted one omission that might be useful - including "is_disabled' in the output. As it doesn't appear to come back when bringing in the Windows users, it might have to be a separate step in the job to update the temp table - like right after the Server_Roles section. I'll be trying to add it myself when I get a chanceGreat idea! I will try to figure out how to do it here as well. the one thing that I see that might make it problematic is when the windows login is through a group; we would only report on the disabled condition of the group, and not of the windows account itself.
Thanks
John.
-
Hi John,
Great script. I just noticed though that the final output does not show deleted windows users. If you add a coalesce statement as below I think that fixes it
Select
DISTINCT
LL.[Account Name]
,@@SERVERNAME as [Database Server]
,UP.dbname as [Database Name]
,coalesce(LoginType,LL.type) as LoginType
--,LL.Privilege
,LL.Server_Roles
,LL.[Permission Path]
,UP.Permissions_user as [User Privileges]
from #LoginsList LL
left join #UserPermissions UP
on LL.[Permission Path] = UP.UserName
-- Comment out the where clause to see all logins that have no database users
-- and their server roles.
-- where exists(select 1 from #LoginsList U2 where U2.[Account Name] = UP.[UserName])
order by
LL.[Account Name]
,UP.DBName;
Regards
Jeff.
-
SQLBlimp (2/18/2016)
Joe O'Connor (2/18/2016)
I did some more experimenting with this, and noted one omission that might be useful - including "is_disabled' in the output. As it doesn't appear to come back when bringing in the Windows users, it might have to be a separate step in the job to update the temp table - like right after the Server_Roles section. I'll be trying to add it myself when I get a chanceGreat idea! I will try to figure out how to do it here as well. the one thing that I see that might make it problematic is when the windows login is through a group; we would only report on the disabled condition of the group, and not of the windows account itself.
Thanks
John.
I figured it out. I ran through the list twice, once for the users, and once to cascade the groups down to the users - which isn't terribly important, you cannot actually disable a windows group within SQL (learned that through this experiment).
Snippet of code to add it is:
-- Get is_disabled into the mix
-- Add column to table
alter table #LoginsList add [is_disabled] int;
update LL
set
is_disabled = p.is_disabled
FROM
#LoginsList LL
INNER JOIN (Select name, is_disabled FROM sys.server_principals) p
ON LL.[Account Name] = p.name
-- cascade the permission path values down to the users granted access from that path
update LL
SET is_disabled = pp.is_disabled
FROM
#LoginsList LL
INNER JOIN (select L2.[Account Name], L2.is_disabled FROM #LoginsList L2) pp
ON LL.[Permission Path] = pp.[Account Name]
then just add the column to the output at the end
-
Joe: Thanks! I truly appreciate it!
John.
-
Thank you as well, this helped out a lot!!!!
-
This is well fabricated one, I am doing something very similar thing RBAC remediation and I am stuck at place where user defined roles got what access , I mean we are moving something from standard roles like db_owner to XXXX_db_owner and the this role should have all or few of the access grants from parent role got. and stuck with get the access grants for these used defined roles , cause when we moved few users are losing their access to certain things. so is there any way or script any one can help me with which can say exactly.
@JayMunnangi
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply