DENY and REVOKE for Roles and Users

Question

DENY and REVOKE for Roles and Users

Ells

SSCrazy Eights

Points: 9901
More actions
January 18, 2013 at 4:50 am

#288178

Trying to understand REVOKE and DENY
If I have
RoleA
UserB
RoleA has select access to the schema data
UserB has select access to the table datatable in the schema data
If UserB is a member of the role RoleA
If I want to remove access to just the table datable for User B.
Do I use DENY SELECT ON dattable TO UserB? Will that DENY statement stop UserB?
If i use Revoke select on datatable for UserB - will it revoke UserA access to the table as the user but they can still see it as a member of the Role?
Just trying to undestand the difference here with regards to DENY\REVOKE specifically to users and roles. Struggling to get my head round this as I feel I am missing something
Many Thnaks
E

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

REVOKE would remove a GRANT from the permissions matrix (if it existed). In my head, i think "UN-GRANT"

DENY would add a new entry to the permissions matrix, and prevent access, even if other permissions seemed to GRANT permissions to the object...so the DENY trumps all, as long as the user is not a sysadmin.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ells SSCrazy Eights Points: 9901 More actions · Answer 2

Are my assumptions correct in that a deny would for USerB overrule the access that it had as USerN and as a member of RoleA?

Its mainly the difference between user and role and the difference you get useing DENY and revoke.

Cheers

Mark

Lowell SSC Guru Points: 323518 More actions · Answer 3

Ells (1/18/2013)
Are my assumptions correct in that a deny would for USerB overrule the access that it had as USerN and as a member of RoleA?
Its mainly the difference between user and role and the difference you get useing DENY and revoke.
Cheers
Mark

you are correct, Mark: whether you place a DENY on the user, or on a group the user belongs to, the DENY will prevail and prevent access;(unless the login for the user is a sysadmin)

it's common to create a role that just has the DENY in it, and then add the user to that new role as well.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

Lowell (1/18/2013)

Ells (1/18/2013)
you are correct, Mark: whether you place a DENY on the user, or on a group the user belongs to, the DENY will prevail and prevent access;(unless the login for the user is a sysadmin)

I agree with everything said but wanted to add just one additional point to the unless portion:

If a user were gaining access to an object via ownership chaining (common when data access is done through a Stored Procedure, Synonym, Function or View) then a DENY also would not apply to the User.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Ells SSCrazy Eights Points: 9901 More actions · Answer 5

Thanks,

it really is just to understand the straightforward way they are different. Will get to the complex bits later.

We can assume it is just two developers typing in SQL in SSMS, no stored procs or weird access routes via applications etc.

Ta

E

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

Ells (1/18/2013)
Thanks,
it really is just to understand the straightforward way they are different. Will get to the complex bits later.
We can assume it is just two developers typing in SQL in SSMS, no stored procs or weird access routes via applications etc.
Ta
E

Sounds good. Stored Procedures are a good thing to keep in the back of your mind for use when you get to the point of wanting to open up database access to an application or another non-developer User. Ownership Chaining is always in the background too, i.e. there is nothing additional to setup, it is just part of how SQL Server lets Users that have permission to execute a Stored Procedure access data in the tables referenced by the Stored Procedure without actually letting them connect to the table directly, a very important security and abstraction concept in SQL Server that it is worth learning early in the process.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato