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