Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Did you revoke access to the [public] role? –  RoKa Apr 19 '13 at 12:06
add comment

1 Answer

up vote 0 down vote accepted

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.

share|improve this answer
    
Thanks for that. I agree it is better to grant permission and deny by default. I think this is the approach I will use, which makes my question moot. –  Salvatore Giacinto Apr 19 '13 at 13:45
    
That said, yes, the user I was testing with was a sysadmin thus it was useless to deny them anything in the database role. –  Salvatore Giacinto Apr 19 '13 at 13:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.