Grant deny permission stacking
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
9
down vote
favorite
For the role db_denycustomer
, I want only the column code of the customer table to be SELECTable, and none of the others. So I did this:
DENY SELECT ON dbo.customer TO db_denycustomer
GRANT SELECT ON dbo.customer (code) TO db_denycustomer
...and it works fine. Cool! But, why?
What I've read in related articles is that permissions stack, but DENY
takes precedence. In contrast, in my case, it seems that the last permission "query" took precedence. Sure enough, if I execute them in reverse order, the latter DENY
hides the code column too.
Could you please elaborate on this?
I have also included the default db_datawriter
and db_datareader
roles to the user that I tested with.
sql-server permissions role
add a comment |Â
up vote
9
down vote
favorite
For the role db_denycustomer
, I want only the column code of the customer table to be SELECTable, and none of the others. So I did this:
DENY SELECT ON dbo.customer TO db_denycustomer
GRANT SELECT ON dbo.customer (code) TO db_denycustomer
...and it works fine. Cool! But, why?
What I've read in related articles is that permissions stack, but DENY
takes precedence. In contrast, in my case, it seems that the last permission "query" took precedence. Sure enough, if I execute them in reverse order, the latter DENY
hides the code column too.
Could you please elaborate on this?
I have also included the default db_datawriter
and db_datareader
roles to the user that I tested with.
sql-server permissions role
add a comment |Â
up vote
9
down vote
favorite
up vote
9
down vote
favorite
For the role db_denycustomer
, I want only the column code of the customer table to be SELECTable, and none of the others. So I did this:
DENY SELECT ON dbo.customer TO db_denycustomer
GRANT SELECT ON dbo.customer (code) TO db_denycustomer
...and it works fine. Cool! But, why?
What I've read in related articles is that permissions stack, but DENY
takes precedence. In contrast, in my case, it seems that the last permission "query" took precedence. Sure enough, if I execute them in reverse order, the latter DENY
hides the code column too.
Could you please elaborate on this?
I have also included the default db_datawriter
and db_datareader
roles to the user that I tested with.
sql-server permissions role
For the role db_denycustomer
, I want only the column code of the customer table to be SELECTable, and none of the others. So I did this:
DENY SELECT ON dbo.customer TO db_denycustomer
GRANT SELECT ON dbo.customer (code) TO db_denycustomer
...and it works fine. Cool! But, why?
What I've read in related articles is that permissions stack, but DENY
takes precedence. In contrast, in my case, it seems that the last permission "query" took precedence. Sure enough, if I execute them in reverse order, the latter DENY
hides the code column too.
Could you please elaborate on this?
I have also included the default db_datawriter
and db_datareader
roles to the user that I tested with.
sql-server permissions role
sql-server permissions role
edited yesterday
Paul Whiteâ¦
46.5k14251398
46.5k14251398
asked yesterday
George Menoutis
1534
1534
add a comment |Â
add a comment |Â
1 Answer
1
active
oldest
votes
up vote
10
down vote
accepted
This is documented behavior provided for backwards compatibility. Documentation excerpt:
Caution A table-level DENY does not take precedence over a
column-level GRANT. This inconsistency in the permissions hierarchy
has been preserved for backward compatibility.
add a comment |Â
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
10
down vote
accepted
This is documented behavior provided for backwards compatibility. Documentation excerpt:
Caution A table-level DENY does not take precedence over a
column-level GRANT. This inconsistency in the permissions hierarchy
has been preserved for backward compatibility.
add a comment |Â
up vote
10
down vote
accepted
This is documented behavior provided for backwards compatibility. Documentation excerpt:
Caution A table-level DENY does not take precedence over a
column-level GRANT. This inconsistency in the permissions hierarchy
has been preserved for backward compatibility.
add a comment |Â
up vote
10
down vote
accepted
up vote
10
down vote
accepted
This is documented behavior provided for backwards compatibility. Documentation excerpt:
Caution A table-level DENY does not take precedence over a
column-level GRANT. This inconsistency in the permissions hierarchy
has been preserved for backward compatibility.
This is documented behavior provided for backwards compatibility. Documentation excerpt:
Caution A table-level DENY does not take precedence over a
column-level GRANT. This inconsistency in the permissions hierarchy
has been preserved for backward compatibility.
answered yesterday
Dan Guzman
12.3k11533
12.3k11533
add a comment |Â
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f217259%2fgrant-deny-permission-stacking%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password