Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kimi86
Yak Posting Veteran
79 Posts |
 Posted - 2013-03-12 : 08:35:25
|
| I nned to restrict some users from reading a table in my databse.This is what I have done so farUnder databse -> Security-> Roles-> Database Roles -> Right Click to create new database roleIn the property window general tabAdd role name (Deny_read) and Owner(dbo)Schema owned by this role - > I did not select anythingRole Members -> Addeda usernameIn the property window Securables tabSecurables -> added the table namePermissions for the table -> check deny selectBut after creating this Role the user is still able to access the table and perform select on it.. Any idea y this could be happening?? |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-03-12 : 10:38:18
|
| What other permissions does this user have or what other roles do they belong? I believe sql "rounds up" when there are conflicting permissions. for example if they are in the fixed server role of serverAdmin then I don't think belonging to Deny_read will have any effect.You will need to look at all permissions granted to these users.EDIT:What I just said was at best simplified and at worst just wrong. Have a look at this: Resolving Permission ConflictsThere are significant differences between "revoke" and "deny".Also, you can check if your deny was implemented correctly or not by running this for your table: exec sp_helprotect '<tableToDenySelect>'Be One with the OptimizerTG |
 |
|
|
|
|
|
deny user READ access to a table