Limiting user access to tables based on a ROLE

Question

I have a SQL Server 2005 database to which I would like to LIMIT access. I have done the following:

• Created a database role called NO_HR
• Added the tables to this role under Securables that should be blocked
• Set all permissions to DENY for these tables
• Assigned my users this role under Database Users -> Role Members

I would expect the user to have whatever public access is allowed to the database but be denied access to the tables as defined in the role.

Problem is, users can access all tables as if the rules in the role are not taken into account. My question: How do I block access to tables based on role membership?

A few facts:

• SQL Server 2005 Standard
• Windows 2003 R2 Enterprise
• Users are DOMAIN logins
• Any help would be appreciated.

SG

Answer 1

If I had to guess you have them added to the db_owner role as well. Either that or sysadmin (although less likely). Administrative roles, specifically DBO and SYSADMIN can not be blocked from access to anything they have control over (dbo - database level, sysadmin - instance level). I would go back and check first the role that you created and make sure that it is not a member of any other roles that might cause an issue. db_datareader for instance shouldn't be a big deal. Second I would check public and make sure IT isn't a member of any other roles. Then I would check any other roles that the users might be a member of.

All that being said it's also possible you made a mistake when you set up your role and revoked instead of denyed (I've done that type of thing before).

Last but not least, you should change how you are doing this anyway. Remove all access from public. Create a role that has just the access you want them to have and add them to that role. It is generally considered a bad idea to grant access to public. Or for that matter to grant lots of access and then deny it to certain people. It's far better to accidently forget to grant someone access to something, than to forget to deny it.