2

I have an application set up in which the service account uses a user defined role which has each object in the database defined with specific permissions. It seems that every table in the database is listed here and they all have update, delete, insert, and select - so, essentially, db_datareader (select) and db_datawriter (insert, update, delete). The role also has select on all views, and execute on all procs.

When a user tries to update a document using a word plugin, with only the user role permissions, the update fails. If i grant db_datawriter on top of the user role to the account and the update is then attempted, it succeeds.

Where could the discrepancy be here? Why if both of these options are essentially giving the same permissions can we be getting different outcomes?

6
  • 3
    Would be curious to see what that pluging is doing on the database. You could set up a trace / extended even to capture the query and the error that SQL raise. That would probably give you a hint for the reason why it fails. Jan 20 '20 at 15:58
  • 1
    Please post the error message and what calls are being made while doing the 'update a document'. Jan 20 '20 at 16:18
  • 1
    to be completely fair, db_datawriter applies at the entire database level - if your perms are applied to specific objects there might be something missing?
    – GMassDBA
    Jan 20 '20 at 17:42
  • @GabSql has a good point. A trigger that updates a log somewhere that is not granted permissions by the explicit list attached to the login, or even if the update itself references a table that is not in the explicit list, would fail, while with the db_datawriter permission, it would succeed. Is there a specific reason for doing this with individual GRANTs instead of group membership? Jan 20 '20 at 19:57
  • Yes you would have hoped the vendor who supplied this would have thought of all of this and given the correct permissions e.g. to triggers accordingly.. problem is this application is just junkware and now I am expected to make it work!
    – jack
    Jan 21 '20 at 8:22
1

You say "It seems that every table in the database is listed here and they all have update, delete, insert, and select". If you have not explicitly verified that insert, update, and delete permissions have been provided on every object via this role; these permissions may be missing on some objects.

The role [db_datawriter] guarantees that insert, update, and delete permissions are provided on all objects within the database. This also includes any future objects to be created.

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.