November 8, 2019 at 7:16 pm
Hi, I came across something that changed the way I see SQL Server permissions.
Way back, we created a LOGIN in SQL Server for an AD group. At the time, we gave the new LOGIN db_datareader permission to a database. As expected, all of the people in the AD group could SELECT from that database.
Later, we dropped the LOGIN but did not drop the database USER that was associated with that LOGIN. We thought that because the database USER was mapped to a LOGIN that no longer existed, it would be a dead end for access.
Some time later, we added a new AD group as a LOGIN and gave it permission to other databases. There is a person in the new AD group who surprisingly gained db_datareader permission on the first database because he is a member of the AD group that is still a USER of the first database (with no matching login) even thought the new LOGIN does not grant access to that first database.
It was a surprise and tells me that when we drop a login, we really do need to clean up the associated database users. It states as much in SSMS when dropping a login but I did not understand the correlation between database users and instance logins that don't have explicit permission to a database.
From Management Studio: "Deleting server logins does not delete the database users associated with the logins. To complete the process, delete the users in each database." <--- this seems important.
November 8, 2019 at 7:57 pm
Sorry, but is there a question?
November 8, 2019 at 8:28 pm
Some time later, we added a new AD group as a LOGIN and gave it permission to other databases. There is a person in the new AD group who surprisingly gained db_datareader permission on the first database because he is a member of the AD group that is still a USER of the first database (with no matching login) even thought the new LOGIN does not grant access to that first database.
This makes sense to me. LOGINs do not have database permissions, they allow authentication and connectivity to an instance. USERs do have database permissions but not connectivity to an instance. I believe your specific case is mentioned in the documentation:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql?view=sql-server-ver15
November 8, 2019 at 9:01 pm
Because logins have user mappings, I though that a login had to be mapped to a database user for the person to get those database permissions. Apparently not... having a LOGIN gets the person in the front door and then if that person has permissions at a database level, the permissions will be effective regardless if their login is mapped to the database user.
This would be a wonderful exam question.
November 9, 2019 at 5:44 pm
Because logins have user mappings, I though that a login had to be mapped to a database user for the person to get those database permissions. Apparently not... having a LOGIN gets the person in the front door and then if that person has permissions at a database level, the permissions will be effective regardless if their login is mapped to the database user.
This would be a wonderful exam question.
But - the login is mapped to a user in the database through the security group. The issue is that the user in the database is a security group with active members so when SQL checks that security group membership - the user exists and is granted access.
I think the confusion is the fact that the security group in the database no longer has a login mapped directly to that security group. This doesn't actually matter because SQL Server is not authenticating the security group - it is authenticating the members in the security group.
The user is able to login to SQL Server because that user is a member of security group A (the member is authenticated) - and gets access to the database because they are a member of security group B (again - the member is authenticated, not the group).
This leads to the next concern - which is that permissions in a database are cumulative. That is - if you have a member in multiple security groups and each security group has been granted different permissions in a database - that member will have *all* permissions associated with both groups. In fact, this is the exact scenario you ran into...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 14, 2019 at 1:06 pm
You can use xp_logininfo 'Domain\Username', 'all' to get some insight into how that user might be getting access to the first db.
One of the items in the permission path column will be mapped to a user or group in the first db.
November 15, 2019 at 5:01 pm
I appreciate you posting this. I reckon the exhibited behavior does make sense, but would not have occurred to me.
November 15, 2019 at 5:13 pm
schleep, not exactly. I too expected that xp_logininfo would show that the user has read permission to the database but it does not. It indicates only that the person gains access to the instance based on the AD security group where they are a member. The AD group does not give permission to the database where the person can SELECT.
The only way I can find to confirm that they have SELECT access to the other database is through "execute as" which will show SELECT.
USE <db_name>;
execute as login = 'domain\account'
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
REVERT;
November 15, 2019 at 6:17 pm
Try this: (Not sure if this works in 2017, setuser was deprecated, but i'm still on 2008):
setuser 'Domain\UserName';
use <db_name>;
setuser;
If you don't get an error, they have access to the DB one way or another.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply