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
• Any help would be appreciated.

SG

• Did you revoke access to the [public] role? – RoKa Apr 19 '13 at 12:06