Skip to content

Instantly share code, notes, and snippets.

@kevinmartintech

kevinmartintech/Database User and Roles Assignment Script.sql

Last active March 12, 2025 14:29
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save kevinmartintech/7e6dcb891c9193b705f0e0e6a7cb2ca1 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save kevinmartintech/7e6dcb891c9193b705f0e0e6a7cb2ca1 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Database User and Roles Assignment Script.sql
/*************************************************************************************************
** # Database User and Roles Assignment Script
**
** If your application uses only one database, a contained user should be created (RECOMMENDED)
** You can create a contained user in each database with the same username and password
** If your application uses multiple databases, you CAN create a SQL Server login (NOT RECOMMENDED)
** You will create a database user from the SQL Server login for each database
** THIS IS NOT PORTABLE! SQL LOGIN MUST BE CREATED ON OTHER SQL SERVERS WITH THE SAME SID.
**
** This script will enabled contained users if it is executed on a SQL Server
** This script will create a contained database user if it does not exist
** This script will add or remove the username from the roles
** This script can add or remove a role for ETL, this allows GRANT ALTER on tables that use TRUNCATE
**
** Change SSMS to connect with your database. "Ctrl+U" then type "[DATABASE-NAME]" and Enter Key.
** Modify the variables to set the database name, username, password, and roles. Then execute.
**
** # Migrate User Created from Login
**
** ## For SQL Server Databases
** Step 1: Ensure the database is setup as a contained database
** https://docs.microsoft.com/en-us/sql/relational-databases/security/contained-database-users-making-your-database-portable?view=sql-server-ver15
** Step 2: EXEC sp_migrate_user_to_contained @username = N'user', @rename = N'keep_name', @disablelogin = N'disable_login';
** https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-migrate-user-to-contained-transact-sql?view=sql-server-ver15
**
** ## For Azure SQL Databases
** Azure SQL Database does not support the system stored procedure sp_migrate_user_to_contained
** You must drop and recreate the database user and login.
** Step 1: DROP LOGIN App_AppName; /* Run in master database */
** Step 2: DROP USER App_AppName; /* Run in app database */
** Step 3: Create a contained user with this script
** An alternative would be to create a new contained database user and change the connection string,
** then drop and the database user and login.
**
*************************************************************************************************/
/* Database Variables */
DECLARE @DatabaseName nvarchar(128) = N'<SQLServerDatabase, ,IgnoreForAzureSQLDatabase>'; /* This is the name of your SQL Server database, used to enable user containment */
/* User Info Variables */
DECLARE
@UserName nvarchar(100) = N'<LoginName, ,App_AppName>' /* Use the naming convention of App_[APPLICATION_NAME] */
,@Password nvarchar(100) = N'<Password, ,ChangeMe123!>' /* Generated from LastPass */
,@IsLoginUser bit = 0 /* 0 = Creates a database contained user, 1 = Creates a login and database contained user */
,@IsLoginCreated bit = 0; /* 0 = The SQL Server login has not been created yet, 1 = The SQL Server login has been created */
/* Common Roles Variables */
DECLARE
@IsDBReader bit = 1 /* 0 = Can NOT select table data, 1 = Can select table data (Set to 0 when app user will only insert/update/execute) */
,@IsDBWriter bit = 1 /* 0 = Can NOT insert/update table data, 1 = Can insert/update table data. (Set to 0 if app user is for reporting) */
,@IsDBExecutor bit = 1; /* 0 = Can NOT execute stored procedures, 1 = Can execute stored procedures. (Set to 0 if app user will not call stored procedures) */
/* Uncommon Roles Variables, talk to the SQL team to make sure you need any of these roles */
DECLARE
@IsDBOwner bit = 0 /* 0 = Does not grant GOD permissions, 1 = Grants GOD permissions (Almost never should an app user be the owner of the database!) */
,@IsDBAccessAdmin bit = 0 /* 0 = Can NOT add or remove access to the database, 1 = Can add or remove access to the database */
,@IsDBBackupOperator bit = 0 /* 0 = Can NOT back up the database, 1 = Can back up the database */
,@IsDBDDLAdmin bit = 0 /* 0 = Can NOT run any Data Definition Language (DDL) command in the database, 1 = Can run any Data Definition Language (DDL) command in the database */
,@IsDBDenyDataReader bit = 0 /* 0 = Does NOT override any read data role permissions, 1 = Cannot read any data in user tables in the database */
,@IsDBDenyDataWriter bit = 0 /* 0 = Does NOT override any add, modify, or delete data role permissions, 1 = Cannot add, modify, or delete any data in user tables in the database */
,@IsDBSecurityAdmin bit = 0 /* 0 = Can NOT modify role membership for custom roles and manage permissions, 1 = Can modify role membership for custom roles and manage permissions */
,@IsETL bit = 0 /* 0 = Can NOT perform tasks like TRUNCATE, 1 = Can perform tasks like TRUNCATE (There are other methods to allow TRUNCATE) */
,@IsETLSchemas nvarchar(MAX) = N'Staging,Integration'; /* Provide a comma delimited list of schemas to GRANT ALTER on */
/*************************************************************************************************
** PRINT the code to create a server level login so it can be used to create a user from
*************************************************************************************************/
IF @IsLoginUser = 1
AND @IsLoginCreated = 0
BEGIN
PRINT '/*************************************************************************************************';
PRINT '** Step 1: Execute this script in the master database in a new query window';
PRINT '** Step 2: Set the varible @IsLoginCreated = 1';
PRINT '** Step 3: Re-run Database User and Roles Assignment Script';
PRINT '*************************************************************************************************/';
IF SERVERPROPERTY('EngineEdition') = 5
BEGIN
PRINT '/* "Ctrl+U" then type "master" then the Enter Key */';
END;
ELSE
BEGIN
PRINT 'USE master;';
PRINT 'GO';
END;
PRINT 'IF NOT EXISTS (SELECT * FROM sys.sql_logins WHERE name = ''' + @UserName + ''')';
PRINT ' BEGIN';
PRINT ' CREATE LOGIN [' + @UserName + '] WITH PASSWORD = ''' + @Password + ''';';
PRINT ' END;';
PRINT '';
PRINT '';
PRINT '';
PRINT '';
RETURN;
END;
/*************************************************************************************************
** Enable Contained Users in SQL Server (does not apply to Azure SQL)
*************************************************************************************************/
IF SERVERPROPERTY('EngineEdition') <> 5
AND @IsLoginUser = 0
BEGIN
DECLARE @StringToExecuteContainedUsers nvarchar(MAX);
PRINT 'Setting SQL Server to allow contained database authentication...';
PRINT 'Altering database to set containment to partial...';
SET @StringToExecuteContainedUsers = N'
USE master;
EXEC sys.sp_configure ''show advanced'', 1;
RECONFIGURE;
EXEC sys.sp_configure ''contained database authentication'', 1;
RECONFIGURE;
ALTER DATABASE ' + QUOTENAME(@DatabaseName) + N' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE ' + QUOTENAME(@DatabaseName) + N' SET CONTAINMENT = PARTIAL;
ALTER DATABASE ' + QUOTENAME(@DatabaseName) + N' SET MULTI_USER;';
EXEC sys.sp_executesql @stmt = @StringToExecuteContainedUsers;
END;
/*************************************************************************************************
** Creating User
*************************************************************************************************/
IF @IsLoginUser = 1
BEGIN
IF DATABASE_PRINCIPAL_ID(@UserName) IS NULL
BEGIN
PRINT 'Creating the user from login...';
EXECUTE (N'CREATE USER [' + @UserName + '] FROM LOGIN [' + @UserName + '];');
END;
ELSE
BEGIN
PRINT 'User already existed, skipping create...';
END;
END;
ELSE
BEGIN
IF DATABASE_PRINCIPAL_ID(@UserName) IS NULL
BEGIN
PRINT 'Creating the contained user...';
EXECUTE (N'CREATE USER [' + @UserName + '] WITH PASSWORD = ''' + @Password + ''';');
END;
ELSE
BEGIN
PRINT 'Contained user already existed, skipping create...';
END;
END;
/*************************************************************************************************
** Creating Roles
*************************************************************************************************/
IF @IsDBExecutor = 1
BEGIN
IF DATABASE_PRINCIPAL_ID('db_executor') IS NULL
BEGIN
PRINT 'Creating db_executor role...';
CREATE ROLE db_executor;
END;
ELSE
BEGIN
PRINT 'The db_executor role already existed, skipping create...';
END;
GRANT EXECUTE TO db_executor;
END;
IF @IsETL = 1
BEGIN
IF DATABASE_PRINCIPAL_ID('db_etl') IS NULL
BEGIN
PRINT 'Creating db_etl role...';
CREATE ROLE db_etl;
END;
ELSE
BEGIN
PRINT 'The db_etl role already existed, skipping create...';
END;
IF LEN(@IsETLSchemas) > 0
BEGIN
DECLARE @StringToExecuteETL nvarchar(MAX) = N'';
SELECT
@StringToExecuteETL = @StringToExecuteETL + N'GRANT ALTER ON SCHEMA :: ' + SS.value + N' TO db_etl;'
FROM
STRING_SPLIT(@IsETLSchemas, ',') AS SS;
PRINT 'Executing: ' + @StringToExecuteETL;
EXEC sys.sp_executesql @stmt = @StringToExecuteETL;
END;
END;
/*************************************************************************************************
** # Denying Execute on Stored Procedures
** If there are certain stored procedures that shouldn't be accessible to the application, you
** can explicitly deny the app user.
*************************************************************************************************/
/* DENY EXECUTE ON dbo.[STORED-PROCEDURE-NAME] TO db_executor; */
/*************************************************************************************************
** Assigning Roles
*************************************************************************************************/
IF @IsDBReader = 1
BEGIN
PRINT 'Adding user to the db_datareader role...';
EXECUTE (N'ALTER ROLE db_datareader ADD MEMBER [' + @UserName + '];');
END;
ELSE
BEGIN
PRINT 'Removing user to the db_datareader role...';
EXECUTE (N'ALTER ROLE db_datareader DROP MEMBER [' + @UserName + '];');
END;
IF @IsDBWriter = 1
BEGIN
PRINT 'Adding user to the db_datawriter role...';
EXECUTE (N'ALTER ROLE db_datawriter ADD MEMBER [' + @UserName + '];');
END;
ELSE
BEGIN
PRINT 'Removing user to the db_datawriter role...';
EXECUTE (N'ALTER ROLE db_datawriter DROP MEMBER [' + @UserName + '];');
END;
IF DATABASE_PRINCIPAL_ID('db_executor') IS NOT NULL
BEGIN
IF @IsDBExecutor = 1
BEGIN
PRINT 'Adding user to the db_executor role...';
EXECUTE (N'ALTER ROLE db_executor ADD MEMBER [' + @UserName + '];');
END;
ELSE
BEGIN
PRINT 'Removing user to the db_executor role...';
EXECUTE (N'ALTER ROLE db_executor DROP MEMBER [' + @UserName + '];');
END;
END;
IF DATABASE_PRINCIPAL_ID('db_etl') IS NOT NULL
BEGIN
IF @IsETL = 1
BEGIN
PRINT 'Adding user to the db_etl role...';
EXECUTE (N'ALTER ROLE db_etl ADD MEMBER [' + @UserName + '];');
END;
ELSE
BEGIN
PRINT 'Removing user to the db_etl role...';
EXECUTE (N'ALTER ROLE db_etl DROP MEMBER [' + @UserName + '];');
END;
END;
IF @IsDBOwner = 1
BEGIN
PRINT 'Adding user to the db_owner role...';
EXECUTE (N'ALTER ROLE db_owner ADD MEMBER [' + @UserName + '];');
END;
ELSE
BEGIN
PRINT 'Removing user from the db_owner role...';
EXECUTE (N'ALTER ROLE db_owner DROP MEMBER [' + @UserName + '];');
END;
IF @IsDBAccessAdmin = 1
BEGIN
PRINT 'Adding user to the db_accessadmin role...';
EXECUTE (N'ALTER ROLE db_accessadmin ADD MEMBER [' + @UserName + '];');
END;
ELSE
BEGIN
PRINT 'Removing user from the db_accessadmin role...';
EXECUTE (N'ALTER ROLE db_accessadmin DROP MEMBER [' + @UserName + '];');
END;
IF @IsDBBackupOperator = 1
BEGIN
PRINT 'Adding user to the db_backupoperator role...';
EXECUTE (N'ALTER ROLE db_backupoperator ADD MEMBER [' + @UserName + '];');
END;
ELSE
BEGIN
PRINT 'Removing user from the db_backupoperator role...';
EXECUTE (N'ALTER ROLE db_backupoperator DROP MEMBER [' + @UserName + '];');
END;
IF @IsDBDDLAdmin = 1
BEGIN
PRINT 'Adding user to the db_ddladmin role...';
EXECUTE (N'ALTER ROLE db_ddladmin ADD MEMBER [' + @UserName + '];');
END;
ELSE
BEGIN
PRINT 'Removing user from the db_ddladmin role...';
EXECUTE (N'ALTER ROLE db_ddladmin DROP MEMBER [' + @UserName + '];');
END;
IF @IsDBDenyDataReader = 1
BEGIN
PRINT 'Adding user to the db_denydatareader role...';
EXECUTE (N'ALTER ROLE db_denydatareader ADD MEMBER [' + @UserName + '];');
END;
ELSE
BEGIN
PRINT 'Removing user from the db_denydatareader role...';
EXECUTE (N'ALTER ROLE db_denydatareader DROP MEMBER [' + @UserName + '];');
END;
IF @IsDBDenyDataWriter = 1
BEGIN
PRINT 'Adding user to the db_denydatawriter role...';
EXECUTE (N'ALTER ROLE db_denydatawriter ADD MEMBER [' + @UserName + '];');
END;
ELSE
BEGIN
PRINT 'Removing user from the db_denydatawriter role...';
EXECUTE (N'ALTER ROLE db_denydatawriter DROP MEMBER [' + @UserName + '];');
END;
IF @IsDBSecurityAdmin = 1
BEGIN
PRINT 'Adding user to the db_securityadmin role...';
EXECUTE (N'ALTER ROLE db_securityadmin ADD MEMBER [' + @UserName + '];');
END;
ELSE
BEGIN
PRINT 'Removing user from the db_securityadmin role...';
EXECUTE (N'ALTER ROLE db_securityadmin DROP MEMBER [' + @UserName + '];');
END;
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment