I don't want a restricted database role to be able to view table and stored procedure definitions and code.
So I do
deny view definition to some_db_role
It does appear though that this is not capable of being overridden.
grant view definition on dbo.some_proc to some_db_role
seems to have no effect.
Worse
grant view definition, references on symmetric key::some_key to some_db_role
seems to have no effect. Basically if you deny view definition at the db level then symmetric keys are unusable by that role\user.
Am I missing something? Is there any way to grant rights to objects that override the rights of the db level deny?
Best Answer
From BOL:
The implication is that when you
DENY
permissions to an object those permissions override anyGRANTed
permissions applicable to that object.Having said that, you don't need to explicitly deny "view definition" permissions on procedures and other code. If you don't grant
VIEW DEFINITION
the definitions are not visible, even if the code can be executed. Tables work slightly differently; you'll need todeny view definition
to any tables the role hasSELECT
rights on.You can test this using the code below.
Create a test database:
Create a table, and a stored proc:
Create a test user, and a test role:
Grant
SELECT
andEXECUTE
to the test objects:Impersonate the user so we can see the effect of the
GRANT
statements above:DENY VIEW DEFINITION
on the table, and test again:Cleanup: