/*
Thanks to Chris K.
Edit the table variables' values for custom roles, users (+ their logins and tiers), and user-role mappings.
Server logins must pre-exist.
*/
--CHANGE THIS: matches databse you are deploying to
USE DataBaser123
--CHANGE THIS: D = Dev, Q = QA , U = UAT , P = Prod, D = DR
DECLARE @tierCode CHAR(1) = 'D'
set nocount on;
-- define custom db roles
declare @customDbRole table (customDbRoleNm sysname not null primary key,
permission nvarchar(4000))
insert @customDbRole (customDbRoleNm, permission)
values ('db_executor', N'grant execute to db_executor');
-- define db users
declare @dbUser table ( dbUserID int not null, -- unique identifier for user that is consistent across tiers
tierCode char(1) not null, -- [D|Q|U|P]
dbUserName sysname not null,
loginName sysname not null,
primary key ( dbUserId,
tierCode ) )
insert @dbUser (dbUserID, tierCode, dbUserName, loginName)
values
-- some users
( 1, 'D', 'domain\user11', 'domain\user11'),
( 1, 'Q', 'domain\user1, 'domain\user1'),
( 1, 'U', 'domain\user1D1', 'domain\user1D1'),
( 1, 'P', 'domain\user1', 'bubba'),
-- ssrs users
( 2, 'D', 'NW\ssrs_user_dev', 'NW\ssrs_user_dev'),
( 2, 'Q', 'NW\ssrs_user_qa', 'NW\ssrs_user_qa'),
( 2, 'U', 'NW\ssrs_user_uat', 'NW\ssrs_user_uat'),
( 2, 'P', 'NW\ssrs_user_prod','NW\ssrs_user_prod'),
-- Informatica Service Accounts
( 3, 'D', 'informatica_read', 'informatica_read'),
( 3, 'Q', 'informatica_read', 'informatica_read'),
( 3, 'U', 'informatica_read', 'informatica_read'),
( 3, 'P', 'informatica_read', 'informatica_read'),
( 4, 'D', 'informatica_write', 'informatica_write'),
( 4, 'Q', 'informatica_write', 'informatica_write'),
( 4, 'U', 'informatica_write', 'informatica_write'),
( 4, 'P', 'informatica_write', 'informatica_write');
/*
define user-role mapping
ROLES: 'db_owner' 'db_datareader' 'db_datawriter' 'db_executor' 'db_ddladmin'
*/
declare @dbUserDbRole table ( dbUserID int not null,
dbRoleNm sysname not null,
primary key (dbUserId, dbRoleNm) )
insert @dbUserDbRole (dbUserID, dbRoleNm)
values (1, 'db_datareader'),
(1, 'db_datawriter'),
(1, 'db_executor'),
(2, 'db_datareader'),
(3, 'db_datareader'),
(3, 'db_executor'),
(4, 'db_datareader'),
(4, 'db_datawriter'),
(4, 'db_executor');
------------------------
-- COMMON BLOCK BELOW --
------------------------
-- derive at which tier permissions are being applied (this relies on using the registry setting or that the
-- servername defining tier as the 2nd character)
DECLARE @outvar varchar(255)
if @outvar is not null
begin
select @tierCode = substring(@outvar,1,1);
end
else
begin
select @tierCode = case when @@servername like 'BV%'
then 'D'
else substring(@@servername,2,1)
end;
end;
declare @printStmt varchar(8000);
select @printStmt = 'Applying permissions for ' +
case when @tierCode = 'D' then 'Development'
when @tierCode = 'Q' then 'QA'
when @tierCode = 'U' then 'UAT'
when @tierCode = 'P' then 'Production'
else 'Uncertain'
end;
print @printStmt;
-- output information about existing users that are untouched:
declare @untouchedUser table ( unTouchedUserNm sysname not null primary key)
insert @untouchedUser ( unTouchedUserNm )
select princ.name
from sys.database_principals princ
where princ.type in (N'U',N'S')
and name not in ('public','dbo','guest','information_schema','sys')
and name not in (select dbUserName from @dbUser where tierCode = @tierCode);
while exists (select 1 from @untouchedUser)
begin
declare @uun sysname;
select top 1 @uun = unTouchedUserNm,
@printStmt = 'The following user exists in the target but is not source controlled: ' +
unTouchedUserNm
from @untouchedUser
order by unTouchedUserNm;
print @printStmt;
delete @untouchedUser
where unTouchedUserNm = @uun;
end;
-- apply permissions
declare @sql nvarchar(4000) = null,
@rc int = null;
-- custom roles
while exists (select 1 from @customDbRole)
begin
declare @customDbRoleNm sysname, @permission nvarchar(4000);
select top 1 @customDbRoleNm = customDbRoleNm, @permission = permission
from @customDbRole
order by customDbRoleNm;
select @sql = N'if not exists (select 1 from sys.database_principals WHERE name = N''' + @customDbRoleNm + ''' and type = ''R'') create role [' + @customDbRoleNm + ']';
exec @rc = sp_executeSql @sql;
if @rc <> 0 or @@error <> 0
begin
raiserror('Error from executeSql on: %s',16,1,@sql);
return;
end;
print @sql;
exec @rc = sp_executeSql @permission;
if @rc <> 0 or @@error <> 0
begin
raiserror('Error from executeSql on: %s',16,1,@permission);
return;
end;
print @permission;
delete @customDbRole
where customDbRoleNm = @customDbRoleNm;
end
-- users and role mapping
while exists (select 1 from @dbUser where tierCode = @tierCode)
begin
declare @dbUserID int, @dbUserNm sysname, @loginNm sysname;
select top 1 @dbUserId = dbUserId, @dbUserNm = dbUserName, @loginNm = loginName
from @dbUser
where tierCode = @tierCode
order by dbUserId;
select @sql = N'if not exists (select 1 from sys.database_principals WHERE name = N''' + @dbUserNm + ''') create user [' + @dbUserNm + '] for login [' + @loginNm + ']';
exec @rc = sp_executeSql @sql;
if @rc <> 0 or @@error <> 0
begin
raiserror('Error from executeSql on: %s',16,1,@sql);
return;
end;
print @sql;
-- add user to roles
while exists (select 1 from @dbUserDbRole where dbUserId = @dbUserId)
begin
declare @dbRoleNm sysname;
select top 1 @dbRoleNm = dbRoleNm
from @dbUserDbRole
where dbUserId = @dbUserId
order by dbRoleNm
select @sql = N'exec sys.sp_addrolemember @rolename = N''' + @dbRoleNm + ''', @membername = N''' + @dbUserNm + '''';
exec @rc = sp_executeSql @sql;
if @rc <> 0 or @@error <> 0
begin
raiserror('Error from executeSql on: %s',16,1,@sql);
return;
end;
print @sql;
delete @dbUserDbRole
where dbUserId = @dbUserId
and dbRoleNm = @dbRoleNm
end
delete @dbUser
where dbUserId = @dbUserID
and tierCode = @tierCode;
end
