Sql-server – db_datawriter vs grant delete, update, insert

permissionsrolesql server

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?

Best Answer

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.

Related Solutions

Sql-server – db_owner Member Can’t Delete on Tables

Unless the users is mapped to the sysadmin fixed server role (where they will connect to the database as the user dbo) then they can be limited by security on the database. (i.e. A member of db_owner will receive a security check where dbo will not.)

A quick test to see if it is a permissions issue is to temporarily grant a user sysadmin role and then see if they can delete data.... if not then I'd start looking for constraints on tables.

If they can then run the following against the database (you may get more you may need to add DELETE to your where clause):

select * from sys.database_permissions 
where state_desc = 'DENY'

If permissions are set it will give you something like:

class class_desc                                                   major_id    minor_id    grantee_principal_id grantor_principal_id type permission_name                                                                                                                  state state_desc
----- ------------------------------------------------------------ ----------- ----------- -------------------- -------------------- ---- -------------------------------------------------------------------------------------------------------------------------------- ----- ------------------------------------------------------------
0     DATABASE                                                     0           0           10                   1                    DL   DELETE                                                                                                                           D     DENY
1     OBJECT_OR_COLUMN                                             1501607433  0           10                   1                    DL   DELETE                                                                                                                           D     DENY

(2 row(s) affected)

If deny delete is set at the DATABASE level then well then there is your problem. Adjust permissions so required users are not actually DENYed access.

Obvious reason Postgres Users can’t read a table

Connect to the respective database by \c DATABASE_NAME
It should have the Connect permission: GRANT CONNECT ON DATABASE DATABASE_NAME TO USER_NAME;
Grant the permission to use the schema GRANT USAGE ON SCHEMA public TO USER_NAME;
Grant permission to select on all the tables GRANT SELECT ON ALL TABLES IN SCHEMA public TO USER_NAME;

Best Answer

Related Solutions

Sql-server – db_owner Member Can’t Delete on Tables

Obvious reason Postgres Users can’t read a table

Related Question