How is he seeing all the databases

  • 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.

     

  • Do any Windows domain groups have SQL Server Windows logins/database access?

  • 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 reply was modified 2 years, 6 months ago by  krypto69.
  • krypto69 wrote:

    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

  • 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.

     

  • 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

  • Thanks Jeffery - Appreciate your help! Gotta be a group membership.

  • 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)

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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