Define SQL Server database permissions for development team
The dba routine includes developer’s accounts management. Such as creating sql logins for new developers. There is usually a standard permissions list and database environment for code writing and testing. Let`s try to simplify the process.
Assume there are two developers.
-- USE master GO CREATE LOGIN [JuniorDeveloper] WITH PASSWORD=N'Qwerty12345' MUST_CHANGE, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON CREATE LOGIN [SeniorDeveloper] WITH PASSWORD=N'Qwerty12345' MUST_CHANGE, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON GO --Create two databases -- CREATE DATABASE sandbox -- CREATE DATABASE uat GO -- with a test table in every base -- USE sandbox GO SELECT * INTO test FROM sys.objects go -- USE uat GO SELECT * INTO test FROM sys.objects GO
Database sandbox is used for development and it should allow all developers to make changes. Database uat is used for automated testing and developers must have read only permissions for it. We`d like to give these permissions for all developers in one shot. We include them into some server role for it.
-- Create server role CREATE SERVER ROLE [dev] GO ALTER SERVER ROLE [dev] ADD MEMBER [JuniorDeveloper] ALTER SERVER ROLE [dev] ADD MEMBER [SeniorDeveloper] GO
New server role dev has got two members. Unfortunately MS SQL allows set permissions for them at the server level, but not at the database level. We can grant them processadmins privileges and etc. but not db_read for uat database. But we can do it by the steps outlined below. Use procedure UsersToDBs for it.
GO -- USE master go IF OBJECT_ID('UsersToDBs') IS NOT NULL DROP PROCEDURE UsersToDBs go CREATE PROCEDURE UsersToDBs @dbname SYSNAME, @role SYSNAME, @permission SYSNAME as DECLARE @MemberName SYSNAME DECLARE @str VARCHAR(1000) SET @str = '-- USE [' + @dbname + '] IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE NAME = ''' + @role + ''') CREATE ROLE [' + @role + '] AUTHORIZATION dbo ' EXEC (@str) SET @str = '-- USE [' + @dbname + '] ALTER ROLE [' + @permission + '] ADD MEMBER [' + @role + ']' EXEC (@str) DECLARE cur cursor FOR SELECT 'MemberName' = S-- USER_NAME(rm.member_principal_id) FROM sys.server_role_members rm JOIN sys.server_principals lgn ON rm.role_principal_id = lgn.principal_id WHERE lgn.name = @role OPEN cur FETCH cur INTO @MemberName WHILE @@fetch_status = 0 BEGIN SET @str = '-- USE ' + @dbname + ' IF NOT EXISTS(SELECT 1 FROM sys.database_principals WHERE name = ''' + @MemberName + ''') CREATE -- USER ' + @MemberName + ' FOR LOGIN ' + @MemberName + ' ELSE EXEC sp_change_users_login ''AUTO_FIX'', ''' + @MemberName + '''' EXEC (@str) SET @str = '-- USE ' + @dbname + ' ALTER role ' + @role + ' ADD MEMBER [' + @MemberName + ']' EXEC (@str) FETCH cur INTO @MemberName END CLOSE cur DEALLOCATE cur GO Using this procedure we can include all dev group members into the same group at database @dbname and set permissions for it. Usage sample: IF OBJECT_ID('SetUsersPermissions') IS NOT NULL DROP PROCEDURE SetUsersPermissions go create PROCEDURE SetUsersPermissions AS DECLARE @dbname SYSNAME, @permission SYSNAME, @role SYSNAME, @str VARCHAR(MAX) --Grant db_datareader for database uat: IF DB_ID('uat') IS NOT NULL begin select @dbname = 'uat', @role = 'dev', @permission = 'db_datareader' EXEC UsersToDBs @dbname = @dbname, @role = @role, @permission = @permission END Grant db_owner for databasе dev: IF DB_ID('sandbox') IS NOT NULL begin select @dbname = 'sandbox', @role = 'dev', @permission = 'db_owner' EXEC UsersToDBs @dbname = @dbname, @role = @role, @permission = @permission END GO EXEC SetUsersPermissions GO --We can check the result by trying to use test tables in our databases: -- USE master EXECUTE AS LOGIN = 'JuniorDeveloper' go -- USE sandbox GO SELECT * FROM test t (83 row affected) DROP TABLE test Command(s) completed successfully. -- USE uat GO SELECT * FROM test t (83 row affected) DROP TABLE test Msg 3701, Level 14, State 20, Line 124 Cannot drop the table 'test', because it does not exist or you do not have permission. GO -- USE master Revert
Great! We can do all actions with objects on dev by JuniorDeveloper but can run select only on uat. I run SetUsersPermissions procedure every time when I create new developer login. Also sometimes I need to grant permissions on tempDB database. Developers wanted to have possibility for creating static tables in it (tables without # and ## prefixes). As we know, tempDB database is recreated every time when server is restarted. I added next commands into SetUsersPermissions:
select @dbname = 'tempDB', @role = 'dev', @permission = 'db_owner' EXEC UsersToDBs @dbname = @dbname, @role = @role, @permission = @permission and set SetUsersPermissions as startup procedure: EXEC sp_procoption @ProcName = 'SetUsersPermissions', @OptionName = 'startup', @OptionValue = 'on'
Now after server restart all dev group members can create static objects in tempDB.
By Alexey Tikhomirov