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
- Added the tables to this role under
Securablesthat should be blocked
- Set all permissions to
DENYfor 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.