Post reply

Does a database role determine what a user can do to objects inside that DB?

  • March 31, 2019 at 8:54 am

    #2220813

    Under a particular database, I click on a user profile and check the properties. The database role of db_datareader is assigned. I had previously assigned the update permission to the 'employee' table too.

    I logged out, then logged back in as the user in question. Under a employee table in the same database, I was able to assign the 'Update' permission. I was then able to change a value in the 'employee' table.

    Updating a value is 'writing' (as opposed to reading). How could this user account write to a table within this database, when the database role only allows read only access?

  • March 31, 2019 at 2:42 pm

    #2246459

    What other roles does the use in question have?

  • March 31, 2019 at 4:29 pm

    #2252751

    For server roles, the user only has 'public.' For database roles the user has db_backupoperator and db_datareader.

  • March 31, 2019 at 4:37 pm

    #2253282

    You stated you had previously granted update access to the user - once granted it will remain until explicitly revoked. So now this user has db_datareader role AND explicit update to the Employee table.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • March 31, 2019 at 9:05 pm

    #2269411

    I understand Update will be in effect until it is revoked. What I am wondering is how is it possible for the user to do a write operation on a table, when the user is assigned read-only access at the database level? I would think that any access assigned at the database level would cascade down to the tables, views and stored procedures within that database.

  • April 1, 2019 at 5:42 pm

    #2389971

    Permissions are cumulative so somewhere along the lines, you have given the user permissions for the update - whether it's through role membership, membership in a windows group with permissions, individual permissions, permissions granted to public,etc.

    So it's possible by having granted the permissions.

    Sue

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply