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?

  • 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. Commented Jan 20, 2020 at 15:58
  • 1
    Please post the error message and what calls are being made while doing the 'update a document'. Commented Jan 20, 2020 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
    Commented Jan 20, 2020 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? Commented Jan 20, 2020 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
    Commented Jan 21, 2020 at 8:22

1 Answer 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 and acknowledge you have read our privacy policy.

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