Understanding SQL Server permissions

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)

• My Server Login can be linked to a Database User

• My Database User can have one or more Database roles

• 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?

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)

StackExchange Database Administrators Q#4619, answer score: 8