0

I have given one data base user read permission only, using db_datareader role. Now, I need to give the same user permission to update only one table (PROD.CustomerRelations).

Is there any way to give this permission ?

EDIT: I have tried the below method, but still I cannot update. I'm accessing SQL Server DB thru Microsoft Access.

Allow update access to only 2 tables in a database schema

I guess the above method does not work because I used Deny for Delete, Update and Insert in the screen Database Properties -> Permissions -> Explicit permissions for [domain\user1]

3

grant update on object::PROD.CustomerRelations to db_user

https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-object-permissions-transact-sql?view=sql-server-2017

  • I guess the above method does not work because I used Deny for Delete, Update and Insert in the screen Database Properties -> Permissions -> Explicit permissions for [domain\user1] – Rauf Jun 13 '18 at 8:01
  • In this case you could create stored procedure that will update table for you and would be executed in context of another user with sufficient permissions (docs.microsoft.com/en-us/sql/t-sql/statements/…) – Grzegorz Lewicki Jun 13 '18 at 8:30
1

DENY takes precedence. So if you at any level has deny,, then you cannot do the operation. (There's one exception, and that is for sysadmins for which SQL Server doesn't check DENY.) So, you cannot have that DENY in place, quite simply. You'd need to specify DENY at a more granular level (or don't specify GRANT in the first place.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.