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?

  • 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. – Dominique Boucher Jan 20 at 15:58
  • 1
    Please post the error message and what calls are being made while doing the 'update a document'. – SqlWorldWide Jan 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 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? – Laughing Vergil Jan 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 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.

| improve this answer | |

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.