Sql-server – Understanding SQL Server permissions

Securitysql serversql-server-2008-r2

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?

Best Answer

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)

Related Solutions

Sql-server – SQL Server Agent not observing “execute as” permissions

Have you checked which SQL user is actually running the query? Depending on the permission level, SQL Agent may be allowing the user to 'impersonate' a higher level account.

Create a job with the T-SQL step:

SELECT ORIGINAL_LOGIN(), SUSER_NAME(), USER_NAME();

Make it log output to a table or text file and review which user is actually running the final query. If it's not the user you think it should be, then you'll need to trace back to work out where the impersonation is coming from.

I have ran into problems on CRM Dynamics databases using filtered views, where specified CRM users can see their filtered data. If the job is using a system account impersonation it'll return no data as the system account doesn't exist as a CRM user.

SQL Server – Can’t Grant Role Permissions Under Sysadmin Login

This is happening because the principal in the AS clause needs to have the permission with the "GRANT OPTION". Running this will allow you to run your original code:

GRANT ALTER ON ROLE::[AdaptTemplate] TO [AdminSapr] WITH GRANT OPTION

My reading of Books Online made me think that what you're doing is right since AdminSapr is in the db_owner role but in testing I had to do something like the above to get this to work.

Best Answer

Related Solutions

Sql-server – SQL Server Agent not observing “execute as” permissions

SQL Server – Can’t Grant Role Permissions Under Sysadmin Login

Related Question