Limit access using domain groups

  • sqlvitoco

    SSC Enthusiast

    Points: 118

    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: 89891

    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: 118

    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!!!!

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

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