August 1, 2019 at 2:04 pm
We are in the midst of eliminating mixed mode and going exclusively to Windows Authentication. I have scripted out permissions for various AD groups for test and production (meaning, I have separate scripts for each environment). I've found that there is a user that is able to edit production that should not be able to (his group is only a db_datareader), and a user that can alter a procedure in test that should not be able to (his group does not have db_ddladmin rights). I have looked at the AD groups these users belong to and do not see how they have permissions to do this in each case and used SSMS to look at these groups to verify they do not have more permissions than are applied in the script. In other words, they are not members of the groups that have these permissions.
So, how does one go about troubleshooting this further?
August 1, 2019 at 3:35 pm
Perhaps the permissions have been granted explicitly rather than through membership of a built-in role. For example, if a user can change a stored procedure, maybe he has ALTER permission on the schema that the procedure is in, or is a member of a role that does?
John
August 1, 2019 at 3:42 pm
imho, there are two options here:
-- check is there a personal login/username in user DB
select * from sys.database_principals where name = 'AD\user'
go
-- check permissions
exec sp_helprotect @username='AD\user'
go
-- check all possible access paths
use master
go
exec xp_loginifo 'AD\user','all'
go
Also, group membership can be nested and xp_logininfo should be executed for each group you get for AD\user:
exec xp_logininfo 'AD\group','members'
August 1, 2019 at 6:58 pm
Try using sys.login_token and sys.user_token to find all the tokens associated with that account. It can pick up universal groups as well as nested groups which can be missed using other methods. This article has further explanations and a demo - you can query those using execute as for the accounts in question:
The Secret of the Security Token – How SQL Server Determines Active Permissions
Sue
August 5, 2019 at 3:12 pm
Also, group membership can be nested and xp_logininfo should be executed for each group you get for AD\user:
exec xp_logininfo 'AD\group','members'
I'm not seeing that xp_logininfo will show nested groups - in my case I'm only seeing users as members of a group where there are at least 2 nested groups that should be listed and they are not. These nested groups account for part of my confusion, but there appears to be more to research.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply