Limit access using domain groups

  • sqlvitoco

    SSC Enthusiast

    Points: 138

    I have the following scenario in a SQL Server 2008 R2 instace:

    - Domain group DOM\Group01:
    . User DOM\User001 member of this group

    - Database GROUP01:
    . Domain user DOM\Group01 with db_owner role

    - Database MAIN:
    . Database user MUSR with db_owner role
    . Table dbo.TAB1
    . MUSR granted select permision on dbo.TAB1 to group DOM\Group01

    When user DOM\User001 connect to the instance, he can create tables in database GROUP01 using both his own schema and dbo schema, but he cannot select records from MAIN.dbo.TAB1, instead, he gets the following error message:

    Msg 916, Level 14, State 1, Line 1
    The server principal "DOM\User001" is not able to access the database "MAIN" under the current security context.

    What am I missing? Is this an AD issue?

    Thanks!!!

  • Sue_H

    SSC Guru

    Points: 90287

    sqlvitoco - Thursday, August 23, 2018 11:04 AM

    I have the following scenario in a SQL Server 2008 R2 instace:

    - Domain group DOM\Group01:
    . User DOM\User001 member of this group

    - Database GROUP01:
    . Domain user DOM\Group01 with db_owner role

    - Database MAIN:
    . Database user MUSR with db_owner role
    . Table dbo.TAB1
    . MUSR granted select permision on dbo.TAB1 to group DOM\Group01

    When user DOM\User001 connect to the instance, he can create tables in database GROUP01 using both his own schema and dbo schema, but he cannot select records from MAIN.dbo.TAB1, instead, he gets the following error message:

    Msg 916, Level 14, State 1, Line 1
    The server principal "DOM\User001" is not able to access the database "MAIN" under the current security context.

    What am I missing? Is this an AD issue?

    Thanks!!!

    It's not an AD issue. The user (or the group depending on what you want to do) needs connect permissions to the database.

    Sue

  • sqlvitoco

    SSC Enthusiast

    Points: 138

    Sue_H - Thursday, August 23, 2018 11:46 AM

    It's not an AD issue. The user (or the group depending on what you want to do) needs connect permissions to the database. 

    Sue

    That was easy... How could I miss that? :blush:

    Thank you, very much!!!!

  • RobertSSmith

    Newbie

    Points: 2

    This reply has been reported for inappropriate content.

    Hi,

    The user definitely needs connect permissions to the database. Well, this are some basic things which the IT department handles like our organization has tied up with the South Florida network support expert services. The IT experts are doing their work excellently. If you are facing any further difficulties then you can contact them and clear your queries.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply