Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Aug 14 '11 at 17:02

This question came from our site for professional and enthusiast programmers.

1 Answer 1

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)

share|improve this answer
    
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 Aug 14 '11 at 18:02
    
@Surfer513: the schema is a placeholder for the role, it has no permissions –  gbn Aug 14 '11 at 18:55
    
Ah, okay I see. Thanks for clearing up the misconception! –  Thomas Stringer Aug 14 '11 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 Aug 16 '11 at 11:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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