Limit access using domain groups

Question

Limit access using domain groups

sqlvitoco

SSC Veteran

Points: 218
More actions
August 23, 2018 at 11:04 am

#362611

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!!!
January 16, 2023 at 10:50 am

This was removed by the editor as SPAM

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

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

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 Veteran Points: 218 More actions · Answer 2

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 More actions · Answer 3

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.