October 13, 2017 at 8:51 am
Hi Experts,
Need a Small information about user access. one of our users is in a group and the group is having read acess to one of our databases. members in the group are able to see all the objects dbo and other schema but not this user, he is able to see only dbo not the other schema owned objects. i checked everything but not able to find any. any clue would be very hlpful.
Thank you in Advance.
October 13, 2017 at 8:58 am
If you run these two statements in the database in question, do you get any results?
SELECT * FROM sys.database_permissions
WHERE state_desc = 'DENY'
SELECT * FROM sys.database_role_members m
JOIN sys.database_principals r
ON m.role_principal_id = r.principal_id
WHERE r.name LIKE '%deny%'
John
Edit: changed member_principal_id to role_principal_id.
October 13, 2017 at 9:01 am
When you say he's in a group, is this an AD group or do you mean a Database role?
Is the user a member of any of AD Groups or Database roles, which have a DENY permission on the other schema? If you give a User/Role both the GRANT and DENY permission, then the DENY overrides the GRANT. If the users are all part of the same AD Group/Database Role, but this one user is part of another as well, then, most likely, the other AD Group/Database Role has DENY on the other Schemas.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 13, 2017 at 9:04 am
John Mitchell-245523 - Friday, October 13, 2017 8:58 AMIf you run these two statements in the database in question, do you get any results?
SELECT * FROM sys.database_permissions
WHERE state_desc = 'DENY'SELECT * FROM sys.database_role_members m
JOIN sys.database_principals r
ON m.member_principal_id = r.principal_id
WHERE r.name LIKE '%deny%'John
Hi John,
yes i get results for the 1st query but not for the second.
October 13, 2017 at 9:07 am
Then I'd guess that the user is also a member of a group that is explicitly denied permissions on the schema. I'm sure you can expand the queries I posted to find out who is denied what permission on what objects.
(By the way, please see my recent edit to my first post - I got the second query wrong.)
John
October 13, 2017 at 9:09 am
Thom A - Friday, October 13, 2017 9:01 AMWhen you say he's in a group, is this an AD group or do you mean a Database role?Is the user a member of any of AD Groups or Database roles, which have a DENY permission on the other schema? If you give a User/Role both the GRANT and DENY permission, then the DENY overrides the GRANT. If the users are all part of the same AD Group/Database Role, but this one user is part of another as well, then, most likely, the other AD Group/Database Role has DENY on the other Schemas.
Hi Thom,
yes the user is in an AD group and the group has db_readonly access to that database. i see connect in the database permissions for that AD group. I don't see any deny permissions on any schema or objects. rest all the user who are part of this group able to view other schema objects. The user is saying when he tries to see the tables using SSMS he can see only dbo not other, where are other users can see.
October 13, 2017 at 9:21 am
ksr39 - Friday, October 13, 2017 9:09 AMHi Thom,
yes the user is in an AD group and the group has db_readonly access to that database. i see connect in the database permissions for that AD group. I don't see any deny permissions on any schema or objects. rest all the user who are part of this group able to view other schema objects. The user is saying when he tries to see the tables using SSMS he can see only dbo not other, where are other users can see.
As John suspects as well, I imagine the user is part of several AD groups. When connecting to SQL Server using AD you inherit all the permissions of any AD groups you are a member of. This, can mean, that two AD Groups could have conflicting permissions; in such as event DENY will always override GRANT.
For example, say you have two AD Groups SalesDept and MarketingDept. SalesDept has SELECT permissions GRANTed on the sales schemes, and DENY on the dbo schema. Members of the MarketingDept have been GRANTed SELECT on both the sales and dbo schemas. If a User is is a member of both the SalesDept and MarketingDept AD Groups they will be DENIED SELECT permissions on the dbo schema. That's because the SalesDept is Denied, and thus so is that user (despite having GRANT in the MarketingDept).
Have a another look at John's second query (post correction) and run it. Does it return any role or groups that have DENY as well? Then, is that user a member of any of those AD Groups or roles that were returned in those queries? If yes, and the DENY is on the other schemas, you have your answer.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 13, 2017 at 9:42 am
Thom A - Friday, October 13, 2017 9:21 AMksr39 - Friday, October 13, 2017 9:09 AMHi Thom,
yes the user is in an AD group and the group has db_readonly access to that database. i see connect in the database permissions for that AD group. I don't see any deny permissions on any schema or objects. rest all the user who are part of this group able to view other schema objects. The user is saying when he tries to see the tables using SSMS he can see only dbo not other, where are other users can see.As John suspects as well, I imagine the user is part of several AD groups. When connecting to SQL Server using AD you inherit all the permissions of any AD groups you are a member of. This, can mean, that two AD Groups could have conflicting permissions; in such as event DENY will always override GRANT.
For example, say you have two AD Groups SalesDept and MarketingDept. SalesDept has SELECT permissions GRANTed on the sales schemes, and DENY on the dbo schema. Members of the MarketingDept have been GRANTed SELECT on both the sales and dbo schemas. If a User is is a member of both the SalesDept and MarketingDept AD Groups they will be DENIED SELECT permissions on the dbo schema. That's because the SalesDept is Denied, and thus so is that user (despite having GRANT in the MarketingDept).
Have a another look at John's second query (post correction) and run it. Does it return any role or groups that have DENY as well? Then, is that user a member of any of those AD Groups or roles that were returned in those queries? If yes, and the DENY is on the other schemas, you have your answer.
I checked for the user if he is part of any other group assigned to any other db's in the server, but he is not in any part of the AD group and he is having access only to that one database. Yes I tried John second query, but it shows no records. I not sure though where i am missing.
October 25, 2017 at 7:12 am
Hi All,
Thank you very much for your valuable suggestions about the issue i faced. i found the solution for this issue, the user is trying to see the schema from SQL Server 2008 version client tools (SSMS) where as the SQL server instance itself is 2012 version. i have asked the user to update his client tools to 2012 or to the latest version and check. the user came back and told the issue got resolved by upgrading to 2012 version for his client tools.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply