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?