SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 deny user READ access to a table		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kimi86
Yak Posting Veteran

54 Posts
Posted - 03/12/2013 :  08:35:25  Show Profile  Reply with Quote
I nned to restrict some users from reading a table in my databse.
This is what I have done so far

Under databse -> Security-> Roles-> Database Roles -> Right Click to create new database role

In the property window general tab
Add role name (Deny_read) and Owner(dbo)
Schema owned by this role - > I did not select anything
Role Members -> Addeda username

In the property window Securables tab
Securables -> added the table name
Permissions for the table -> check deny select


But 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
Flowing Fount of Yak Knowledge

USA
5469 Posts
Posted - 03/12/2013 :  10:38:18  Show Profile  Reply with Quote
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 Conflicts
There 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 Optimizer
TG
Edited by - TG on 03/12/2013 10:54:29
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000