May 5, 2022 at 8:06 pm
Hi,
I have a user that doesn't have access to a database but yet he can see it and query it in SSMS. He is not a member of sysadmin roles or any other roles (there are no custom roles).
I disabled his windows auth account and he was then unable to log in - so I know that he is at least authenticating using that account.
If I go into logins and look at his account it only list 4 DB's that he should have access to yet he can see 30 DB's.
I feel like I have checked everywhere but clearly I am missing something.
Any help greatly appreciated.
May 5, 2022 at 8:24 pm
Do any Windows domain groups have SQL Server Windows logins/database access?
May 5, 2022 at 9:45 pm
Good thought. Yes, but none of the groups he is in have access to the same DB's as he does. (bangs head on wall)
This doesn't make sense to me - not sure what you are trying to state. If he is a member of GROUP1 - and that group has access to DB1 then he will have access to DB1 through that group membership. So are you trying to say these other groups he is a member of - also have not been granted access to these 'other' databases he is able to access?
If that is the case, then you need to check further - check the public server role as well as public in each database. There are permissions defined somewhere granting access to this user and generally it is membership in a group or role if not directly tied to the login/user.
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
May 5, 2022 at 10:16 pm
Sorry bad response.
are you trying to say these other groups he is a member of - also have not been granted access to these 'other' databases he is able to access? Yes - for all the existing groups on the instance - none of the DB access matches the access this user has. There are only 5 groups and they each have limited access. Whereas the user can see almost everything.
Checked the public role on server and each DB - looked fine. Checked all the other roles just for fun. Also dropped and recreated his user.
May 6, 2022 at 4:29 pm
The account is able to access only what has been granted to the login in SQL Server and the user in each database. If this account has higher level of permissions - then it must be due to membership in one or more roles or groups.
When I run into these kinds of issues, I double-check the account directly in AD and validate the groups. I have found that I often need to dig down through group membership to other groups just to find the actual group. Sometimes I can back-track from a group that has permissions in SQL Server to find that group has a member that is a group - which has a member which is a group, etc.
Either way - the login is a member of one or more groups and/or roles that are granting that level of access.
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
May 6, 2022 at 4:49 pm
Thanks Jeffery - Appreciate your help! Gotta be a group membership.
May 6, 2022 at 6:22 pm
If you're not seeing it via direct group membership, look at nested groups (user is a member of a group that is a member of another group that has database access)
May 6, 2022 at 11:12 pm
Shot in the dark but, possibly an easy way to find out how he has access is to create a user using his windows login name and then use xp_logininfo 'hislogin','All' to have SQL Server interrogate AD and find out how he has access through other groups.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 11, 2022 at 6:28 am
This was removed by the editor as SPAM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply