9

I am rooting around in SQL Server Management Studio against my instance of SQL Server 2008 R2 Express Edition. I am trying to understand how the permissions work.

What I can see is (via the properties of many of these entities)

  1. My Server Login can be linked to a Database User
  2. My Database User can have one or more Database roles
  3. One of the Database Roles is db_datawriter which owns schema db_datawriter

However at that point the trail goes cold. Schema db_datawriter has a permissions page under its properties which is blank.

What defines precisely what schema db_datawriter's permissions are?

Improve this question
0

1 Answer 1

Reset to default
8

db_datawriter has no items in the permissions page because it doesn't have explicit object permissions as such. Rights are implied by the role.

MSDN for db_datawriter says

Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

It has INSERT, UPDATE, DELETE on tables as per another MSDB page Permissions of Fixed Database Roles

Granted: DELETE, INSERT, UPDATE

Finally, what does the DB engine say (SQL Server 2008 R2)?

EXEC sp_dbfixedrolepermission 'db_datawriter'

db_datawriter   DELETE permission on any object
db_datawriter   INSERT permission on any object
db_datawriter   UPDATE permission on any object

The MSDN pages for SQL Server 2008 are here (different page hierarchy)

Improve this answer
4
  • I think what the OP is asking is about the permissions on the schema, not the fixed db role. Unless I misread that?
    – Thomas Stringer
    Commented Aug 14, 2011 at 18:02
  • @Surfer513: the schema is a placeholder for the role, it has no permissions
    – gbn
    Commented Aug 14, 2011 at 18:55
  • Ah, okay I see. Thanks for clearing up the misconception!
    – Thomas Stringer
    Commented Aug 14, 2011 at 19:07
  • I was actually trying to find out whether db_datawriter gave you the right to write through a view. I think the answer is that you can if you have permissions on the underlying table (and meet the other conditions of only inserting to one table). My failure to write turned out to be a reseed problem with the identity column and failed on the underlying table as well
    – akc42
    Commented Aug 16, 2011 at 11:54

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.