Custom Database Roles in SQL Server

In addition to the default Database-Level Roles in SQL Server (db_owner, db_securityadmin, db_accessadmin, db_backupoperator, db_ddladmin, db_datawriter, db_datareader, db_denydatawriter and db_denydatareader), you can create custom roles at the database level.  One reason you might want to do this would be if you have different user accounts for the same functionality across multiple environments (e.g. Dev, QA, UAT, Production).  It might be easier for an application to use the same service user/password combination across all of these environments, but then your developers would know the production Password.  If you store any sort of sensitive information in your database, this makes you an easy target.  It is much more secure to use a dedicated service user for each application in each environment.

Another reason you might want to consider using custom roles would be if multiple users in the same department log directly into the database instead of going through a thread pool in the application layer.  If a new user is hired for accounting or maybe someone leaves the department, there is no need to search for which permissions must be added or removed.

With a little bit of work, you can assign all of the database permissions for a user (or group of users) to a custom role.  If you need to add/remove users, you only need to move them in and out of custom roles.  If you need a redacted copy of the Production database brought down to Dev, just swap out the Production service user for the Dev one.  In this example, the accounting application might have four service users (AcctApp_DEV, AcctApp_QA, AcctApp_UAT and AcctApp).  The same could hold true for the sales application, etc.

USE AdventureWorks;
GO

-- Create custom role and add permission(s)
CREATE ROLE db_CUSTOMROLE_AccountingApplication;
GO

GRANT EXECUTE ON dbo.uspGetBillOfMaterials TO db_CUSTOMROLE_AccountingApplication;
GO

--===================================================================

-- Create user and add as member of custom role
CREATE USER [AdventureWorks\AcctApp_DEV] FOR LOGIN [AdventureWorks\AcctApp_DEV];
GO

-- Remove Production user from custom role
EXEC sp_droprolemember N'db_CUSTOMROLE_AccountingApplication', N'AdventureWorks\AcctApp';
GO

EXEC sp_addrolemember N'db_CUSTOMROLE_AccountingApplication', N'AdventureWorks\AcctApp_DEV';
GO

 

Advertisements

#custom-database-roles, #sql-server