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

Rate

Share

Share

Rate