SQL Server–Server versus Database Roles for application developers

App developers typically require ownership of the database that they are working on. This translates to giving them dbo (DB Owner) rights on the database. A dbo can do almost anything – including drop a database. To prevent an accidental dropping of the databasee, the sysadmin can write a trigger that restricts ANYONE (except a sysadmin) from dropping a database.

CREATE TRIGGER [deny_my_db_drop]
ON ALL SERVER
FOR DROP_database
AS
IF IS_SRVROLEMEMBER('sysadmin') = 0
BEGIN
    PRINT ‘drop not permitted'
    ROLLBACK
END
GO

A quick refresher on SQL Server Server Roles versus Database Roles

Server Roles

SQL Server comes with the following fixed server roles. These include:

• sysadmin
• bulkadmin
• dbcreator
• diskadmin
• processadmin
• securityadmin
• serveradmin
• setupadmin

Sysadmin

Sysadmin members can do anything within SQL Server. It bypasses all security checks. So even if you are able to set up a scenario that should block a login from doing something, by nature of the login being a member of this role, it will ignore that restriction. As a result, membership in this role should be carefully controlled. In typical production environments you would only see DBAs being a member of this role.

Database Roles

Within each database, SQL Server does have fixed database roles, ones which are standard and included in every database. These differ from the database roles you can create and use yourself in that they have pre-assigned permissions. The fixed database roles are:

• db_owner
• db_securityadmin
• db_accessadmin
• db_backupoperator
• db_ddladmin
• db_datareader
• db_datawriter
• db_denydatareader
• db_denydatawriter

db_owner

A member of the db_owner role can do anything inside the database. Now there is a difference between a member of the db_owner role and the dbo user. That difference is that if someone maps into the database as the dbo user, that person bypasses all security checks. An example of this is anyone who is a member of the sysadmin fixed server role. They map in as dbo.

Things to remember:

• The db_owner role allows a user to do anything within the database.
• DBAs who are already members of the sysadmin fixed server role come in as dbo and don’t need this role explicitly granted to them.
• Normal users should not be a member of this role.
• Applications might require their user account to be a member of this role.