SQL 2005 - Permission problem with Database roles

Question

SQL 2005 - Permission problem with Database roles

see-heng.chan-639839

Grasshopper

Points: 12
More actions
September 29, 2010 at 9:16 pm

#140838

Hi,
I've been stuck with this problem and it's doing my head in.
I have the following:
Database 1
Database 2
Windows Group 1
Windows Group 2
Windows Group 1 is given Datareader role on Database 1.
Windows Group 2 is given Datareader role on Database 2 and Restricted_View_Role* on Database 1.
*Restricted _View_Role has select permission on a limited number of views on Database 1.
When a user is a member of both Windows Group 1 and 2. The user can only see the views of the Restricted_View_role on Database 1.
However, when I tried this on our test SQL Server, this is not the case. the user can see all tables on Database 1.
anyone have any ideas.
sorry if this has been asked before. I don't even know how to search for this.

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

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

Lowell SSC Guru Points: 323755 More actions · Answer 1

i think you want to avoid giving the db_datareader role in database 1.

the two roles are additive..meaning db_datareader can read EVERY view and table...so adding them to a "restricted" role gains nothing, as the first role gives them more rights than you wanted.

if you belong to three roles, for example, only explicit DENY permissions will prevent a user from seeing something...meaning if role1 and role2 grant permissions to SELECT form certain objects, a third role with DENY would take it away.

remove the role db_datareader from anyone in Database 1, and then they will only have access to the items filtered by your restricted role.

see-heng.chan-639839 (9/29/2010)
Hi,
I've been stuck with this problem and it's doing my head in.
I have the following:
Database 1
Database 2
Windows Group 1
Windows Group 2
Windows Group 1 is given Datareader role on Database 1.
Windows Group 2 is given Datareader role on Database 2 and Restricted_View_Role* on Database 1.
*Restricted _View_Role has select permission on a limited number of views on Database 1.
When a user is a member of both Windows Group 1 and 2. The user can only see the views of the Restricted_View_role on Database 1.
However, when I tried this on our test SQL Server, this is not the case. the user can see all tables on Database 1.
anyone have any ideas.
sorry if this has been asked before. I don't even know how to search for this.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

see-heng.chan-639839 Grasshopper Points: 12 More actions · Answer 2

Yes, I realise the roles are additive but want I wanted is for the user to have the datareader role on Database 1 (ie. to see all the tables) and not the restricted_view_role. This has not been the case previously, not sure what was changed on the SQL Server to make this the case