1113 lines
32 KiB
Transact-SQL
1113 lines
32 KiB
Transact-SQL
USE Arizona
|
|
GO
|
|
|
|
|
|
|
|
/*=============================================================================
|
|
|
|
Script to create dbRoleTPPos role.
|
|
|
|
Role Name: dbRoleTPPos
|
|
TEMPLATE: Default
|
|
|
|
EXAMPLE OF SECURABLES TO SET:
|
|
|
|
INSERT INTO #Securables
|
|
VALUES
|
|
('DB','GRANT','VIEW DEFINITION','',''),
|
|
('SCHEMA','GRANT','SELECT,EXECUTE,INSERT,UPDATE,DELETE','','AP'),
|
|
('TABLE','GRANT','INSERT,UPDATE','Address','dbo'),
|
|
('SP','GRANT','EXECUTE','sp_bmc_GetNextID','dbo'),
|
|
('ROLE','GRANT','db_datareader','','')
|
|
|
|
=============================================================================*/
|
|
|
|
DECLARE @Command nvarchar(max),
|
|
@RoleName varchar(60),
|
|
@Users varchar(255),
|
|
@typeofobject varchar(50),
|
|
@grantordeny varchar(10),
|
|
@rightsaction varchar(255),
|
|
@objectname varchar(255),
|
|
@schemaid varchar(10),
|
|
@sysTarget varchar(255),
|
|
@sysType varchar(255)
|
|
|
|
SET @RoleName = 'dbRoleTPPos'
|
|
SET @Command = ''
|
|
|
|
/* TEMP TABLES */
|
|
CREATE TABLE #UsersOnRole(username varchar(255))
|
|
CREATE TABLE #Securables(typeofobject varchar(50), grantordeny varchar(10), rightsaction varchar(255), objectname varchar(255), schemaid varchar(10),N2 bit)
|
|
|
|
/* !!! LIST OF SECURABLES TO CHANGE !!! */
|
|
|
|
INSERT INTO #Securables
|
|
VALUES
|
|
('ROLE','GRANT','DB_DATAREADER','','dbo',0),
|
|
('ROLE','GRANT','DB_DATAWRITER','','dbo',0)
|
|
|
|
/* GET ALL USERS ON THIS ROLE */
|
|
INSERT INTO #UsersOnRole
|
|
SELECT
|
|
members.name
|
|
FROM sys.database_role_members
|
|
JOIN sys.database_principals roles ON database_role_members.role_principal_id = roles.principal_id
|
|
JOIN sys.database_principals members ON database_role_members.member_principal_id = members.principal_id
|
|
WHERE roles.name = @RoleName
|
|
|
|
/* CREATE ROLE */
|
|
SELECT @Command = '
|
|
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''' + @RoleName + ''' AND type = ''R'')
|
|
BEGIN
|
|
CREATE ROLE [' + @RoleName + '] AUTHORIZATION [dbo]
|
|
PRINT ''CREATE ROLE [' + @RoleName + ']''
|
|
END
|
|
'
|
|
EXEC sp_executesql @Command
|
|
|
|
SET @Command = '
|
|
DECLARE @SP_Name varchar(255)
|
|
|
|
'
|
|
|
|
/* SET ALL ROLE SECURABLES */
|
|
DECLARE SecurablesCurs CURSOR FOR
|
|
SELECT typeofobject, grantordeny, rightsaction, objectname, schemaid
|
|
FROM #Securables
|
|
WHERE N2 = 0
|
|
|
|
OPEN SecurablesCurs
|
|
FETCH NEXT FROM SecurablesCurs INTO @typeofobject, @grantordeny, @rightsaction, @objectname, @schemaid
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
/* ONLY IF SQL VERSION IS LOWER THAN 2022 AND OPERATION IS ACTION IS UNMASK */
|
|
IF ((@rightsaction = 'UNMASK') AND (CONVERT(INT,LEFT(REPLACE(CAST(SERVERPROPERTY('ProductVersion') AS CHAR(15)),'.',''),7)) < 1600000))
|
|
BEGIN
|
|
SET @Command = @Command + @grantordeny + ' ' + @rightsaction + ' TO [' + @RoleName + ']
|
|
'
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
|
|
IF @typeofobject = 'COLUMN'
|
|
BEGIN
|
|
SET @Command = @Command + @grantordeny + ' ' + @rightsaction + ' ON ' + @schemaid + '.' + @objectname + ' TO [' + @RoleName + ']
|
|
'
|
|
END
|
|
|
|
IF @typeofobject = 'DB'
|
|
BEGIN
|
|
IF EXISTS(SELECT 1 FROM sys.fn_builtin_permissions('DATABASE') WHERE permission_name = '' + @rightsaction + '')
|
|
BEGIN
|
|
SET @Command = @Command + @grantordeny + ' ' + @rightsaction + ' TO [' + @RoleName + ']
|
|
'
|
|
END
|
|
END
|
|
|
|
IF @typeofobject = 'SCHEMA'
|
|
BEGIN
|
|
SET @Command = @Command + '
|
|
IF EXISTS (SELECT 1 FROM sys.schemas WHERE name = ''' + @schemaid + ''')
|
|
BEGIN
|
|
IF EXISTS(SELECT 1 FROM sys.fn_builtin_permissions(''SCHEMA'') WHERE permission_name = ''' + @rightsaction + ''')
|
|
BEGIN
|
|
' + @grantordeny + ' ' + @rightsaction + ' ON SCHEMA::' + @schemaid + ' TO [' + @RoleName + ']
|
|
END
|
|
END
|
|
|
|
'
|
|
END
|
|
|
|
IF @typeofobject in('TABLE','SP','FUNCTIONS','VIEWS')
|
|
BEGIN
|
|
|
|
IF @objectname like '%[%]%'
|
|
BEGIN
|
|
|
|
IF @typeofobject = 'TABLE'
|
|
BEGIN
|
|
SET @sysTarget = 'sys.tables'
|
|
SET @sysType = ''
|
|
END
|
|
|
|
IF @typeofobject = 'SP'
|
|
BEGIN
|
|
SET @sysTarget = 'sys.procedures'
|
|
SET @sysType = ''
|
|
END
|
|
|
|
IF @typeofobject = 'FUNCTIONS'
|
|
BEGIN
|
|
SET @sysTarget = 'sys.objects'
|
|
SET @sysType = ' and p.Type IN ( N''FN'', N''IF'', N''TF'', N''FS'', N''FT'' )'
|
|
END
|
|
|
|
IF @typeofobject = 'VIEWS'
|
|
BEGIN
|
|
SET @sysTarget = 'sys.views'
|
|
SET @sysType = ''
|
|
END
|
|
|
|
SET @Command = @Command + '
|
|
DECLARE SP_cursor CURSOR FOR
|
|
SELECT p.name FROM ' + @sysTarget + ' p JOIN sys.schemas s ON s.schema_id = p.schema_id WHERE (p.name like ''' + @objectname + ''') and s.name = ''' + @schemaid + '' + @sysType + '''
|
|
|
|
OPEN SP_cursor
|
|
FETCH NEXT FROM SP_cursor INTO @SP_Name
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
IF EXISTS(SELECT 1 FROM sys.fn_builtin_permissions(''OBJECT'') WHERE permission_name = ''' + @rightsaction + ''')
|
|
BEGIN
|
|
EXEC(''' + @grantordeny + ' ' + @rightsaction + ' ON [' + @schemaid + '].['' + @SP_Name + ''] TO [' + @RoleName + ']'')
|
|
END
|
|
FETCH NEXT FROM SP_cursor INTO @SP_Name
|
|
END
|
|
|
|
CLOSE SP_cursor
|
|
DEALLOCATE SP_cursor
|
|
|
|
'
|
|
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
|
|
SET @Command = @Command + '
|
|
IF EXISTS(SELECT 1 FROM sys.objects o WITH (NOLOCK) JOIN sys.schemas s ON s.schema_id = o.schema_id WHERE o.name = ''' + @objectname + ''' AND o.type IN (N''U'',''P'',''V'',''FN'',''IF'',''TF'') AND s.name = ''' + @schemaid + ''')
|
|
BEGIN
|
|
IF EXISTS(SELECT 1 FROM sys.fn_builtin_permissions(''OBJECT'') WHERE permission_name = ''' + @rightsaction + ''')
|
|
BEGIN
|
|
' + @grantordeny + ' ' + @rightsaction + ' ON [' + @schemaid + '].[' + @objectname + '] TO [' + @RoleName + ']
|
|
END
|
|
END
|
|
|
|
'
|
|
END
|
|
END
|
|
|
|
IF @typeofobject = 'ASSEMBLIES'
|
|
BEGIN
|
|
SET @Command = @Command + '
|
|
IF EXISTS(SELECT 1 FROM sys.assemblies WHERE NAME = ''' + @objectname + ''')
|
|
BEGIN
|
|
IF EXISTS(SELECT 1 FROM sys.fn_builtin_permissions(''ASSEMBLY'') WHERE permission_name = ''' + @rightsaction + ''')
|
|
BEGIN
|
|
' + @grantordeny + ' ' + @rightsaction + '::[' + @objectname + '] TO [' + @RoleName + ']
|
|
END
|
|
END
|
|
|
|
'
|
|
END
|
|
|
|
IF @typeofobject = 'ROLE'
|
|
BEGIN
|
|
SET @Command = @Command + '
|
|
IF NOT EXISTS (SELECT 1 FROM sys.database_role_members JOIN sys.database_principals roles ON database_role_members.role_principal_id = roles.principal_id JOIN sys.database_principals members ON database_role_members.member_principal_id = members.principal_id WHERE roles.name = ''' + @rightsaction + ''' AND members.name = ''' + @RoleName + ''')
|
|
BEGIN
|
|
EXEC sp_addrolemember N''' + @rightsaction + ''', N''' + @RoleName + '''
|
|
END
|
|
|
|
'
|
|
END
|
|
END
|
|
|
|
FETCH NEXT FROM SecurablesCurs INTO @typeofobject, @grantordeny, @rightsaction, @objectname, @schemaid
|
|
END
|
|
|
|
CLOSE SecurablesCurs
|
|
DEALLOCATE SecurablesCurs
|
|
|
|
PRINT 'SET ALL SECURABLES ON ROLE [' + @RoleName + ']'
|
|
EXEC sp_executesql @Command
|
|
|
|
/* ADD USER */
|
|
DECLARE UsersCurs CURSOR FOR
|
|
SELECT username
|
|
FROM #UsersOnRole
|
|
|
|
OPEN UsersCurs
|
|
|
|
FETCH NEXT FROM UsersCurs INTO @Users
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
SELECT @Command = '
|
|
EXEC sp_addrolemember N''' + @RoleName + ''', N''' + @Users + '''
|
|
PRINT ''ADD USER [' + @Users + '] ON ROLE [' + @RoleName + ']''
|
|
'
|
|
|
|
FETCH NEXT FROM UsersCurs INTO @Users
|
|
END
|
|
|
|
CLOSE UsersCurs
|
|
DEALLOCATE UsersCurs
|
|
|
|
EXEC sp_executesql @Command
|
|
|
|
|
|
/*=============================================================================
|
|
Drop temp tables
|
|
=============================================================================*/
|
|
DROP TABLE #Securables
|
|
DROP TABLE #UsersOnRole
|
|
|
|
/*=============================================================================
|
|
Script to create / map all users
|
|
=============================================================================*/
|
|
|
|
DECLARE @username varchar(255),
|
|
@Database VARCHAR(255)
|
|
|
|
SET @username = ''
|
|
SET @Command = ''
|
|
SET @Database = ''
|
|
|
|
CREATE TABLE #AllUsersAndRoles(databasename varchar(255), rolename varchar(255), username varchar(255))
|
|
|
|
INSERT INTO #AllUsersAndRoles
|
|
VALUES
|
|
('Arizona','dbRoleTPPos','sqlLksrvTPPosusr')
|
|
|
|
DECLARE MapUsersAndRolesCurs CURSOR FOR
|
|
SELECT databasename, rolename, username FROM #AllUsersAndRoles AUAR JOIN master.sys.databases D ON D.name = AUAR.databasename
|
|
|
|
OPEN MapUsersAndRolesCurs
|
|
FETCH NEXT FROM MapUsersAndRolesCurs INTO @Database, @rolename, @username
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
SET @Command = 'USE ' + @Database + '
|
|
IF EXISTS (SELECT 1 FROM master.dbo.syslogins WHERE name = ''' + @username + ''')
|
|
BEGIN
|
|
IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N''' + @rolename + ''' AND [type] = ''R'')
|
|
BEGIN
|
|
IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N''' + @username + ''')
|
|
BEGIN
|
|
ALTER USER [' + @username + '] WITH LOGIN = [' + @username + ']
|
|
EXEC sp_addrolemember N''' + @rolename + ''', N''' + @username + '''
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
CREATE USER [' + @username + '] FOR LOGIN [' + @username + '] WITH DEFAULT_SCHEMA=[dbo]
|
|
EXEC sp_addrolemember N''' + @rolename + ''', N''' + @username + '''
|
|
END
|
|
END
|
|
END
|
|
'
|
|
|
|
EXEC sp_executesql @Command
|
|
|
|
FETCH NEXT FROM MapUsersAndRolesCurs INTO @Database, @rolename, @username
|
|
END
|
|
|
|
CLOSE MapUsersAndRolesCurs
|
|
DEALLOCATE MapUsersAndRolesCurs
|
|
|
|
DROP TABLE #AllUsersAndRoles
|
|
GO
|
|
|
|
|
|
|
|
USE Arizona
|
|
GO
|
|
|
|
|
|
|
|
/*=============================================================================
|
|
|
|
Script to create dbRoleIttechUsr role.
|
|
|
|
Role Name: dbRoleIttechUsr
|
|
TEMPLATE: N2 and PROD
|
|
|
|
EXAMPLE OF SECURABLES TO SET:
|
|
|
|
INSERT INTO #Securables
|
|
VALUES
|
|
('DB','GRANT','VIEW DEFINITION','',''),
|
|
('SCHEMA','GRANT','SELECT,EXECUTE,INSERT,UPDATE,DELETE','','AP'),
|
|
('TABLE','GRANT','INSERT,UPDATE','Address','dbo'),
|
|
('SP','GRANT','EXECUTE','sp_bmc_GetNextID','dbo'),
|
|
('ROLE','GRANT','db_datareader','','')
|
|
|
|
=============================================================================*/
|
|
|
|
DECLARE @Type varchar(6),
|
|
@Command nvarchar(max),
|
|
@RoleName varchar(60),
|
|
@Users varchar(255),
|
|
@typeofobject varchar(50),
|
|
@grantordeny varchar(10),
|
|
@rightsaction varchar(255),
|
|
@objectname varchar(255),
|
|
@schemaid varchar(10),
|
|
@sysTarget varchar(255),
|
|
@sysType varchar(255)
|
|
|
|
SET @RoleName = 'dbRoleIttechUsr'
|
|
SET @Command = ''
|
|
|
|
SELECT @Type = [Type] FROM [master].[cfg].[InstanceContext]
|
|
|
|
/* TEMP TABLES */
|
|
CREATE TABLE #UsersOnRole(username varchar(255))
|
|
CREATE TABLE #Securables(typeofobject varchar(50), grantordeny varchar(10), rightsaction varchar(255), objectname varchar(255), schemaid varchar(10),N2 bit)
|
|
|
|
/* !!! LIST OF SECURABLES TO CHANGE !!! */
|
|
|
|
INSERT INTO #Securables
|
|
VALUES
|
|
('DB','GRANT','EXECUTE','','dbo',0),
|
|
('ROLE','GRANT','DB_DATAREADER','','dbo',0),
|
|
('ROLE','GRANT','DB_DATAWRITER','','dbo',0),
|
|
('ROLE','GRANT','DB_OWNER','','dbo',1)
|
|
|
|
/* GET ALL USERS ON THIS ROLE */
|
|
INSERT INTO #UsersOnRole
|
|
SELECT
|
|
members.name
|
|
FROM sys.database_role_members
|
|
JOIN sys.database_principals roles ON database_role_members.role_principal_id = roles.principal_id
|
|
JOIN sys.database_principals members ON database_role_members.member_principal_id = members.principal_id
|
|
WHERE roles.name = @RoleName
|
|
|
|
/* CREATE ROLE */
|
|
SELECT @Command = '
|
|
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''' + @RoleName + ''' AND type = ''R'')
|
|
BEGIN
|
|
CREATE ROLE [' + @RoleName + '] AUTHORIZATION [dbo]
|
|
PRINT ''CREATE ROLE [' + @RoleName + ']''
|
|
END
|
|
'
|
|
EXEC sp_executesql @Command
|
|
|
|
SET @Command = '
|
|
DECLARE @SP_Name varchar(255)
|
|
|
|
'
|
|
|
|
/* SET ALL ROLE SECURABLES */
|
|
IF @Type = 'DEVE'
|
|
BEGIN
|
|
|
|
DECLARE SecurablesCurs CURSOR FOR
|
|
SELECT typeofobject, grantordeny, rightsaction, objectname, schemaid
|
|
FROM #Securables
|
|
WHERE N2 = 1
|
|
|
|
OPEN SecurablesCurs
|
|
FETCH NEXT FROM SecurablesCurs INTO @typeofobject, @grantordeny, @rightsaction, @objectname, @schemaid
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
/* ONLY IF SQL VERSION IS LOWER THAN 2022 AND OPERATION IS ACTION IS UNMASK */
|
|
IF ((@rightsaction = 'UNMASK') AND (CONVERT(INT,LEFT(REPLACE(CAST(SERVERPROPERTY('ProductVersion') AS CHAR(15)),'.',''),7)) < 1600000))
|
|
BEGIN
|
|
SET @Command = @Command + @grantordeny + ' ' + @rightsaction + ' TO [' + @RoleName + ']
|
|
'
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
|
|
IF @typeofobject = 'COLUMN'
|
|
BEGIN
|
|
SET @Command = @Command + @grantordeny + ' ' + @rightsaction + ' ON ' + @schemaid + '.' + @objectname + ' TO [' + @RoleName + ']
|
|
'
|
|
END
|
|
|
|
IF @typeofobject = 'DB'
|
|
BEGIN
|
|
IF EXISTS(SELECT 1 FROM sys.fn_builtin_permissions('DATABASE') WHERE permission_name = '' + @rightsaction + '')
|
|
BEGIN
|
|
SET @Command = @Command + @grantordeny + ' ' + @rightsaction + ' TO [' + @RoleName + ']
|
|
'
|
|
END
|
|
END
|
|
|
|
IF @typeofobject = 'SCHEMA'
|
|
BEGIN
|
|
SET @Command = @Command + '
|
|
IF EXISTS (SELECT 1 FROM sys.schemas WHERE name = ''' + @schemaid + ''')
|
|
BEGIN
|
|
IF EXISTS(SELECT 1 FROM sys.fn_builtin_permissions(''SCHEMA'') WHERE permission_name = ''' + @rightsaction + ''')
|
|
BEGIN
|
|
' + @grantordeny + ' ' + @rightsaction + ' ON SCHEMA::' + @schemaid + ' TO [' + @RoleName + ']
|
|
END
|
|
END
|
|
|
|
'
|
|
END
|
|
|
|
IF @typeofobject in('TABLE','SP','FUNCTIONS','VIEWS')
|
|
BEGIN
|
|
|
|
IF @objectname like '%[%]%'
|
|
BEGIN
|
|
IF @typeofobject = 'TABLE'
|
|
BEGIN
|
|
SET @sysTarget = 'sys.tables'
|
|
SET @sysType = ''
|
|
END
|
|
|
|
IF @typeofobject = 'SP'
|
|
BEGIN
|
|
SET @sysTarget = 'sys.procedures'
|
|
SET @sysType = ''
|
|
END
|
|
|
|
IF @typeofobject = 'FUNCTIONS'
|
|
BEGIN
|
|
SET @sysTarget = 'sys.objects'
|
|
SET @sysType = ' and p.Type IN ( N''FN'', N''IF'', N''TF'', N''FS'', N''FT'' )'
|
|
END
|
|
|
|
IF @typeofobject = 'VIEWS'
|
|
BEGIN
|
|
SET @sysTarget = 'sys.views'
|
|
SET @sysType = ''
|
|
END
|
|
|
|
SET @Command = @Command + '
|
|
DECLARE SP_cursor CURSOR FOR
|
|
SELECT p.name FROM ' + @sysTarget + ' p JOIN sys.schemas s ON s.schema_id = p.schema_id WHERE (p.name like ''' + @objectname + ''') and s.name = ''' + @schemaid + '' + @sysType + '''
|
|
|
|
OPEN SP_cursor
|
|
FETCH NEXT FROM SP_cursor INTO @SP_Name
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
IF EXISTS(SELECT 1 FROM sys.fn_builtin_permissions(''OBJECT'') WHERE permission_name = ''' + @rightsaction + ''')
|
|
BEGIN
|
|
EXEC(''' + @grantordeny + ' ' + @rightsaction + ' ON [' + @schemaid + '].['' + @SP_Name + ''] TO [' + @RoleName + ']'')
|
|
END
|
|
FETCH NEXT FROM SP_cursor INTO @SP_Name
|
|
END
|
|
|
|
CLOSE SP_cursor
|
|
DEALLOCATE SP_cursor
|
|
|
|
'
|
|
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
|
|
SET @Command = @Command + '
|
|
IF EXISTS(SELECT 1 FROM sys.objects o WITH (NOLOCK) JOIN sys.schemas s ON s.schema_id = o.schema_id WHERE o.name = ''' + @objectname + ''' AND o.type IN (N''U'',''P'',''V'',''FN'',''IF'',''TF'') AND s.name = ''' + @schemaid + ''')
|
|
BEGIN
|
|
IF EXISTS(SELECT 1 FROM sys.fn_builtin_permissions(''OBJECT'') WHERE permission_name = ''' + @rightsaction + ''')
|
|
BEGIN
|
|
' + @grantordeny + ' ' + @rightsaction + ' ON [' + @schemaid + '].[' + @objectname + '] TO [' + @RoleName + ']
|
|
END
|
|
END
|
|
|
|
'
|
|
END
|
|
END
|
|
|
|
IF @typeofobject = 'ASSEMBLIES'
|
|
BEGIN
|
|
SET @Command = @Command + '
|
|
IF EXISTS(SELECT 1 FROM sys.assemblies WHERE NAME = ''' + @objectname + ''')
|
|
BEGIN
|
|
IF EXISTS(SELECT 1 FROM sys.fn_builtin_permissions(''ASSEMBLY'') WHERE permission_name = ''' + @rightsaction + ''')
|
|
BEGIN
|
|
' + @grantordeny + ' ' + @rightsaction + '::[' + @objectname + '] TO [' + @RoleName + ']
|
|
END
|
|
END
|
|
|
|
'
|
|
END
|
|
|
|
IF @typeofobject = 'ROLE'
|
|
BEGIN
|
|
SET @Command = @Command + '
|
|
IF NOT EXISTS (SELECT 1 FROM sys.database_role_members JOIN sys.database_principals roles ON database_role_members.role_principal_id = roles.principal_id JOIN sys.database_principals members ON database_role_members.member_principal_id = members.principal_id WHERE roles.name = ''' + @rightsaction + ''' AND members.name = ''' + @RoleName + ''')
|
|
BEGIN
|
|
EXEC sp_addrolemember N''' + @rightsaction + ''', N''' + @RoleName + '''
|
|
END
|
|
|
|
'
|
|
END
|
|
END
|
|
|
|
FETCH NEXT FROM SecurablesCurs INTO @typeofobject, @grantordeny, @rightsaction, @objectname, @schemaid
|
|
END
|
|
|
|
CLOSE SecurablesCurs
|
|
DEALLOCATE SecurablesCurs
|
|
|
|
PRINT 'SET ALL SECURABLES ON ROLE [' + @RoleName + ']'
|
|
EXEC sp_executesql @Command
|
|
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
|
|
DECLARE SecurablesCurs CURSOR FOR
|
|
SELECT typeofobject, grantordeny, rightsaction, objectname, schemaid
|
|
FROM #Securables
|
|
WHERE N2 = 0
|
|
|
|
OPEN SecurablesCurs
|
|
FETCH NEXT FROM SecurablesCurs INTO @typeofobject, @grantordeny, @rightsaction, @objectname, @schemaid
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
/* ONLY IF SQL VERSION IS LOWER THAN 2022 AND OPERATION IS ACTION IS UNMASK */
|
|
IF ((@rightsaction = 'UNMASK') AND (CONVERT(INT,LEFT(REPLACE(CAST(SERVERPROPERTY('ProductVersion') AS CHAR(15)),'.',''),7)) < 1600000))
|
|
BEGIN
|
|
SET @Command = @Command + @grantordeny + ' ' + @rightsaction + ' TO [' + @RoleName + ']
|
|
'
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
|
|
IF @typeofobject = 'COLUMN'
|
|
BEGIN
|
|
SET @Command = @Command + @grantordeny + ' ' + @rightsaction + ' ON ' + @schemaid + '.' + @objectname + ' TO [' + @RoleName + ']
|
|
'
|
|
END
|
|
|
|
IF @typeofobject = 'DB'
|
|
BEGIN
|
|
IF EXISTS(SELECT 1 FROM sys.fn_builtin_permissions('DATABASE') WHERE permission_name = '' + @rightsaction + '')
|
|
BEGIN
|
|
SET @Command = @Command + @grantordeny + ' ' + @rightsaction + ' TO [' + @RoleName + ']
|
|
'
|
|
END
|
|
END
|
|
|
|
IF @typeofobject = 'SCHEMA'
|
|
BEGIN
|
|
SET @Command = @Command + '
|
|
IF EXISTS (SELECT 1 FROM sys.schemas WHERE name = ''' + @schemaid + ''')
|
|
BEGIN
|
|
IF EXISTS(SELECT 1 FROM sys.fn_builtin_permissions(''SCHEMA'') WHERE permission_name = ''' + @rightsaction + ''')
|
|
BEGIN
|
|
' + @grantordeny + ' ' + @rightsaction + ' ON SCHEMA::' + @schemaid + ' TO [' + @RoleName + ']
|
|
END
|
|
END
|
|
|
|
'
|
|
END
|
|
|
|
IF @typeofobject in('TABLE','SP','FUNCTIONS','VIEWS')
|
|
BEGIN
|
|
|
|
IF @objectname like '%[%]%'
|
|
BEGIN
|
|
|
|
IF @typeofobject = 'TABLE'
|
|
BEGIN
|
|
SET @sysTarget = 'sys.tables'
|
|
SET @sysType = ''
|
|
END
|
|
|
|
IF @typeofobject = 'SP'
|
|
BEGIN
|
|
SET @sysTarget = 'sys.procedures'
|
|
SET @sysType = ''
|
|
END
|
|
|
|
IF @typeofobject = 'FUNCTIONS'
|
|
BEGIN
|
|
SET @sysTarget = 'sys.objects'
|
|
SET @sysType = ' and p.Type IN ( N''FN'', N''IF'', N''TF'', N''FS'', N''FT'' )'
|
|
END
|
|
|
|
IF @typeofobject = 'VIEWS'
|
|
BEGIN
|
|
SET @sysTarget = 'sys.views'
|
|
SET @sysType = ''
|
|
END
|
|
|
|
SET @Command = @Command + '
|
|
DECLARE SP_cursor CURSOR FOR
|
|
SELECT p.name FROM ' + @sysTarget + ' p JOIN sys.schemas s ON s.schema_id = p.schema_id WHERE (p.name like ''' + @objectname + ''') and s.name = ''' + @schemaid + '' + @sysType + '''
|
|
|
|
OPEN SP_cursor
|
|
FETCH NEXT FROM SP_cursor INTO @SP_Name
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
IF EXISTS(SELECT 1 FROM sys.fn_builtin_permissions(''OBJECT'') WHERE permission_name = ''' + @rightsaction + ''')
|
|
BEGIN
|
|
EXEC(''' + @grantordeny + ' ' + @rightsaction + ' ON [' + @schemaid + '].['' + @SP_Name + ''] TO [' + @RoleName + ']'')
|
|
END
|
|
FETCH NEXT FROM SP_cursor INTO @SP_Name
|
|
END
|
|
|
|
CLOSE SP_cursor
|
|
DEALLOCATE SP_cursor
|
|
|
|
'
|
|
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
|
|
SET @Command = @Command + '
|
|
IF EXISTS(SELECT 1 FROM sys.objects o WITH (NOLOCK) JOIN sys.schemas s ON s.schema_id = o.schema_id WHERE o.name = ''' + @objectname + ''' AND o.type IN (N''U'',''P'',''V'',''FN'',''IF'',''TF'') AND s.name = ''' + @schemaid + ''')
|
|
BEGIN
|
|
IF EXISTS(SELECT 1 FROM sys.fn_builtin_permissions(''OBJECT'') WHERE permission_name = ''' + @rightsaction + ''')
|
|
BEGIN
|
|
' + @grantordeny + ' ' + @rightsaction + ' ON [' + @schemaid + '].[' + @objectname + '] TO [' + @RoleName + ']
|
|
END
|
|
END
|
|
|
|
'
|
|
END
|
|
END
|
|
|
|
IF @typeofobject = 'ASSEMBLIES'
|
|
BEGIN
|
|
SET @Command = @Command + '
|
|
IF EXISTS(SELECT 1 FROM sys.assemblies WHERE NAME = ''' + @objectname + ''')
|
|
BEGIN
|
|
IF EXISTS(SELECT 1 FROM sys.fn_builtin_permissions(''ASSEMBLY'') WHERE permission_name = ''' + @rightsaction + ''')
|
|
BEGIN
|
|
' + @grantordeny + ' ' + @rightsaction + '::[' + @objectname + '] TO [' + @RoleName + ']
|
|
END
|
|
END
|
|
|
|
'
|
|
END
|
|
|
|
IF @typeofobject = 'ROLE'
|
|
BEGIN
|
|
SET @Command = @Command + '
|
|
IF NOT EXISTS (SELECT 1 FROM sys.database_role_members JOIN sys.database_principals roles ON database_role_members.role_principal_id = roles.principal_id JOIN sys.database_principals members ON database_role_members.member_principal_id = members.principal_id WHERE roles.name = ''' + @rightsaction + ''' AND members.name = ''' + @RoleName + ''')
|
|
BEGIN
|
|
EXEC sp_addrolemember N''' + @rightsaction + ''', N''' + @RoleName + '''
|
|
END
|
|
|
|
'
|
|
END
|
|
END
|
|
|
|
FETCH NEXT FROM SecurablesCurs INTO @typeofobject, @grantordeny, @rightsaction, @objectname, @schemaid
|
|
END
|
|
|
|
CLOSE SecurablesCurs
|
|
DEALLOCATE SecurablesCurs
|
|
|
|
PRINT 'SET ALL SECURABLES ON ROLE [' + @RoleName + ']'
|
|
EXEC sp_executesql @Command
|
|
|
|
END
|
|
|
|
/* ADD USER */
|
|
DECLARE UsersCurs CURSOR FOR
|
|
SELECT username
|
|
FROM #UsersOnRole
|
|
|
|
OPEN UsersCurs
|
|
|
|
FETCH NEXT FROM UsersCurs INTO @Users
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
SELECT @Command = '
|
|
EXEC sp_addrolemember N''' + @RoleName + ''', N''' + @Users + '''
|
|
PRINT ''ADD USER [' + @Users + '] ON ROLE [' + @RoleName + ']''
|
|
'
|
|
|
|
FETCH NEXT FROM UsersCurs INTO @Users
|
|
END
|
|
|
|
CLOSE UsersCurs
|
|
DEALLOCATE UsersCurs
|
|
|
|
EXEC sp_executesql @Command
|
|
|
|
/*=============================================================================
|
|
Drop temp tables
|
|
=============================================================================*/
|
|
DROP TABLE #Securables
|
|
DROP TABLE #UsersOnRole
|
|
/*=============================================================================
|
|
Script to create / map all users
|
|
=============================================================================*/
|
|
|
|
DECLARE @username varchar(255),
|
|
@Database VARCHAR(255)
|
|
|
|
SET @username = ''
|
|
SET @Command = ''
|
|
SET @Database = ''
|
|
|
|
CREATE TABLE #AllUsersAndRoles(databasename varchar(255), rolename varchar(255), username varchar(255))
|
|
|
|
INSERT INTO #AllUsersAndRoles
|
|
VALUES
|
|
('Arizona','dbRoleIttechUsr','AMAVITA\L-AM-AP-SQL-AMA-Pharmacy_Servers_IT_Field'),
|
|
('Arizona','dbRoleIttechUsr','CENTRALINFRA\L-CI-AP-SQL-AMA-Pharmacy_Servers_IT_Field'),
|
|
('Arizona','dbRoleIttechUsr','CENTRALINFRA\L-CI-AP-SQL-CVI-Pharmacy_Servers_IT_Field'),
|
|
('Arizona','dbRoleIttechUsr','CENTRALINFRA\L-CI-AP-SQL-SUN-Pharmacy_Servers_IT_Field'),
|
|
('Arizona','dbRoleIttechUsr','COOP-VITALITY\L-CV-AP-SQL-CVI-Pharmacy_Servers_IT_Field'),
|
|
('Arizona','dbRoleIttechUsr','SUNSTORE\L-SU-AP-SQL-SUN-Pharmacy_Servers_IT_Field')
|
|
|
|
DECLARE MapUsersAndRolesCurs CURSOR FOR
|
|
SELECT databasename, rolename, username FROM #AllUsersAndRoles AUAR JOIN master.sys.databases D ON D.name = AUAR.databasename
|
|
|
|
OPEN MapUsersAndRolesCurs
|
|
FETCH NEXT FROM MapUsersAndRolesCurs INTO @Database, @rolename, @username
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
SET @Command = 'USE ' + @Database + '
|
|
IF EXISTS (SELECT 1 FROM master.dbo.syslogins WHERE name = ''' + @username + ''')
|
|
BEGIN
|
|
IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N''' + @rolename + ''' AND [type] = ''R'')
|
|
BEGIN
|
|
IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N''' + @username + ''')
|
|
BEGIN
|
|
ALTER USER [' + @username + '] WITH LOGIN = [' + @username + ']
|
|
EXEC sp_addrolemember N''' + @rolename + ''', N''' + @username + '''
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
CREATE USER [' + @username + '] FOR LOGIN [' + @username + '] WITH DEFAULT_SCHEMA=[dbo]
|
|
EXEC sp_addrolemember N''' + @rolename + ''', N''' + @username + '''
|
|
END
|
|
END
|
|
END
|
|
'
|
|
|
|
EXEC sp_executesql @Command
|
|
|
|
FETCH NEXT FROM MapUsersAndRolesCurs INTO @Database, @rolename, @username
|
|
END
|
|
|
|
CLOSE MapUsersAndRolesCurs
|
|
DEALLOCATE MapUsersAndRolesCurs
|
|
|
|
DROP TABLE #AllUsersAndRoles
|
|
GO
|
|
|
|
|
|
|
|
USE Arizona
|
|
GO
|
|
|
|
|
|
|
|
/*=============================================================================
|
|
|
|
Script to create dbRoleDevUsr role.
|
|
|
|
Role Name: dbRoleDevUsr
|
|
TEMPLATE: Default
|
|
|
|
EXAMPLE OF SECURABLES TO SET:
|
|
|
|
INSERT INTO #Securables
|
|
VALUES
|
|
('DB','GRANT','VIEW DEFINITION','',''),
|
|
('SCHEMA','GRANT','SELECT,EXECUTE,INSERT,UPDATE,DELETE','','AP'),
|
|
('TABLE','GRANT','INSERT,UPDATE','Address','dbo'),
|
|
('SP','GRANT','EXECUTE','sp_bmc_GetNextID','dbo'),
|
|
('ROLE','GRANT','db_datareader','','')
|
|
|
|
=============================================================================*/
|
|
|
|
DECLARE @Command nvarchar(max),
|
|
@RoleName varchar(60),
|
|
@Users varchar(255),
|
|
@typeofobject varchar(50),
|
|
@grantordeny varchar(10),
|
|
@rightsaction varchar(255),
|
|
@objectname varchar(255),
|
|
@schemaid varchar(10),
|
|
@sysTarget varchar(255),
|
|
@sysType varchar(255)
|
|
|
|
SET @RoleName = 'dbRoleDevUsr'
|
|
SET @Command = ''
|
|
|
|
/* TEMP TABLES */
|
|
CREATE TABLE #UsersOnRole(username varchar(255))
|
|
CREATE TABLE #Securables(typeofobject varchar(50), grantordeny varchar(10), rightsaction varchar(255), objectname varchar(255), schemaid varchar(10),N2 bit)
|
|
|
|
/* !!! LIST OF SECURABLES TO CHANGE !!! */
|
|
|
|
INSERT INTO #Securables
|
|
VALUES
|
|
('ROLE','GRANT','DB_DATAWRITER','','dbo',0),
|
|
('DB','GRANT','SHOWPLAN','','dbo',0),
|
|
('SP','GRANT','ALTER','%PH_Streamfact%','dbo',0),
|
|
('DB','GRANT','VIEW DEFINITION','','dbo',0),
|
|
('TABLE','GRANT','VIEW CHANGE TRACKING','Document_header','dbo',0),
|
|
('ROLE','GRANT','DB_DATAREADER','','dbo',0),
|
|
('TABLE','GRANT','VIEW CHANGE TRACKING','PH_prescription_line','dbo',0),
|
|
('DB','GRANT','EXECUTE','','dbo',0),
|
|
('TABLE','GRANT','VIEW CHANGE TRACKING','Document_line','dbo',0),
|
|
('SP','GRANT','ALTER','%PH_Invoice%','dbo',0)
|
|
|
|
/* GET ALL USERS ON THIS ROLE */
|
|
INSERT INTO #UsersOnRole
|
|
SELECT
|
|
members.name
|
|
FROM sys.database_role_members
|
|
JOIN sys.database_principals roles ON database_role_members.role_principal_id = roles.principal_id
|
|
JOIN sys.database_principals members ON database_role_members.member_principal_id = members.principal_id
|
|
WHERE roles.name = @RoleName
|
|
|
|
/* CREATE ROLE */
|
|
SELECT @Command = '
|
|
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''' + @RoleName + ''' AND type = ''R'')
|
|
BEGIN
|
|
CREATE ROLE [' + @RoleName + '] AUTHORIZATION [dbo]
|
|
PRINT ''CREATE ROLE [' + @RoleName + ']''
|
|
END
|
|
'
|
|
EXEC sp_executesql @Command
|
|
|
|
SET @Command = '
|
|
DECLARE @SP_Name varchar(255)
|
|
|
|
'
|
|
|
|
/* SET ALL ROLE SECURABLES */
|
|
DECLARE SecurablesCurs CURSOR FOR
|
|
SELECT typeofobject, grantordeny, rightsaction, objectname, schemaid
|
|
FROM #Securables
|
|
WHERE N2 = 0
|
|
|
|
OPEN SecurablesCurs
|
|
FETCH NEXT FROM SecurablesCurs INTO @typeofobject, @grantordeny, @rightsaction, @objectname, @schemaid
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
/* ONLY IF SQL VERSION IS LOWER THAN 2022 AND OPERATION IS ACTION IS UNMASK */
|
|
IF ((@rightsaction = 'UNMASK') AND (CONVERT(INT,LEFT(REPLACE(CAST(SERVERPROPERTY('ProductVersion') AS CHAR(15)),'.',''),7)) < 1600000))
|
|
BEGIN
|
|
SET @Command = @Command + @grantordeny + ' ' + @rightsaction + ' TO [' + @RoleName + ']
|
|
'
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
|
|
IF @typeofobject = 'COLUMN'
|
|
BEGIN
|
|
SET @Command = @Command + @grantordeny + ' ' + @rightsaction + ' ON ' + @schemaid + '.' + @objectname + ' TO [' + @RoleName + ']
|
|
'
|
|
END
|
|
|
|
IF @typeofobject = 'DB'
|
|
BEGIN
|
|
IF EXISTS(SELECT 1 FROM sys.fn_builtin_permissions('DATABASE') WHERE permission_name = '' + @rightsaction + '')
|
|
BEGIN
|
|
SET @Command = @Command + @grantordeny + ' ' + @rightsaction + ' TO [' + @RoleName + ']
|
|
'
|
|
END
|
|
END
|
|
|
|
IF @typeofobject = 'SCHEMA'
|
|
BEGIN
|
|
SET @Command = @Command + '
|
|
IF EXISTS (SELECT 1 FROM sys.schemas WHERE name = ''' + @schemaid + ''')
|
|
BEGIN
|
|
IF EXISTS(SELECT 1 FROM sys.fn_builtin_permissions(''SCHEMA'') WHERE permission_name = ''' + @rightsaction + ''')
|
|
BEGIN
|
|
' + @grantordeny + ' ' + @rightsaction + ' ON SCHEMA::' + @schemaid + ' TO [' + @RoleName + ']
|
|
END
|
|
END
|
|
|
|
'
|
|
END
|
|
|
|
IF @typeofobject in('TABLE','SP','FUNCTIONS','VIEWS')
|
|
BEGIN
|
|
|
|
IF @objectname like '%[%]%'
|
|
BEGIN
|
|
|
|
IF @typeofobject = 'TABLE'
|
|
BEGIN
|
|
SET @sysTarget = 'sys.tables'
|
|
SET @sysType = ''
|
|
END
|
|
|
|
IF @typeofobject = 'SP'
|
|
BEGIN
|
|
SET @sysTarget = 'sys.procedures'
|
|
SET @sysType = ''
|
|
END
|
|
|
|
IF @typeofobject = 'FUNCTIONS'
|
|
BEGIN
|
|
SET @sysTarget = 'sys.objects'
|
|
SET @sysType = ' and p.Type IN ( N''FN'', N''IF'', N''TF'', N''FS'', N''FT'' )'
|
|
END
|
|
|
|
IF @typeofobject = 'VIEWS'
|
|
BEGIN
|
|
SET @sysTarget = 'sys.views'
|
|
SET @sysType = ''
|
|
END
|
|
|
|
SET @Command = @Command + '
|
|
DECLARE SP_cursor CURSOR FOR
|
|
SELECT p.name FROM ' + @sysTarget + ' p JOIN sys.schemas s ON s.schema_id = p.schema_id WHERE (p.name like ''' + @objectname + ''') and s.name = ''' + @schemaid + '' + @sysType + '''
|
|
|
|
OPEN SP_cursor
|
|
FETCH NEXT FROM SP_cursor INTO @SP_Name
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
IF EXISTS(SELECT 1 FROM sys.fn_builtin_permissions(''OBJECT'') WHERE permission_name = ''' + @rightsaction + ''')
|
|
BEGIN
|
|
EXEC(''' + @grantordeny + ' ' + @rightsaction + ' ON [' + @schemaid + '].['' + @SP_Name + ''] TO [' + @RoleName + ']'')
|
|
END
|
|
FETCH NEXT FROM SP_cursor INTO @SP_Name
|
|
END
|
|
|
|
CLOSE SP_cursor
|
|
DEALLOCATE SP_cursor
|
|
|
|
'
|
|
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
|
|
SET @Command = @Command + '
|
|
IF EXISTS(SELECT 1 FROM sys.objects o WITH (NOLOCK) JOIN sys.schemas s ON s.schema_id = o.schema_id WHERE o.name = ''' + @objectname + ''' AND o.type IN (N''U'',''P'',''V'',''FN'',''IF'',''TF'') AND s.name = ''' + @schemaid + ''')
|
|
BEGIN
|
|
IF EXISTS(SELECT 1 FROM sys.fn_builtin_permissions(''OBJECT'') WHERE permission_name = ''' + @rightsaction + ''')
|
|
BEGIN
|
|
' + @grantordeny + ' ' + @rightsaction + ' ON [' + @schemaid + '].[' + @objectname + '] TO [' + @RoleName + ']
|
|
END
|
|
END
|
|
|
|
'
|
|
END
|
|
END
|
|
|
|
IF @typeofobject = 'ASSEMBLIES'
|
|
BEGIN
|
|
SET @Command = @Command + '
|
|
IF EXISTS(SELECT 1 FROM sys.assemblies WHERE NAME = ''' + @objectname + ''')
|
|
BEGIN
|
|
IF EXISTS(SELECT 1 FROM sys.fn_builtin_permissions(''ASSEMBLY'') WHERE permission_name = ''' + @rightsaction + ''')
|
|
BEGIN
|
|
' + @grantordeny + ' ' + @rightsaction + '::[' + @objectname + '] TO [' + @RoleName + ']
|
|
END
|
|
END
|
|
|
|
'
|
|
END
|
|
|
|
IF @typeofobject = 'ROLE'
|
|
BEGIN
|
|
SET @Command = @Command + '
|
|
IF NOT EXISTS (SELECT 1 FROM sys.database_role_members JOIN sys.database_principals roles ON database_role_members.role_principal_id = roles.principal_id JOIN sys.database_principals members ON database_role_members.member_principal_id = members.principal_id WHERE roles.name = ''' + @rightsaction + ''' AND members.name = ''' + @RoleName + ''')
|
|
BEGIN
|
|
EXEC sp_addrolemember N''' + @rightsaction + ''', N''' + @RoleName + '''
|
|
END
|
|
|
|
'
|
|
END
|
|
END
|
|
|
|
FETCH NEXT FROM SecurablesCurs INTO @typeofobject, @grantordeny, @rightsaction, @objectname, @schemaid
|
|
END
|
|
|
|
CLOSE SecurablesCurs
|
|
DEALLOCATE SecurablesCurs
|
|
|
|
PRINT 'SET ALL SECURABLES ON ROLE [' + @RoleName + ']'
|
|
EXEC sp_executesql @Command
|
|
|
|
/* ADD USER */
|
|
DECLARE UsersCurs CURSOR FOR
|
|
SELECT username
|
|
FROM #UsersOnRole
|
|
|
|
OPEN UsersCurs
|
|
|
|
FETCH NEXT FROM UsersCurs INTO @Users
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
SELECT @Command = '
|
|
EXEC sp_addrolemember N''' + @RoleName + ''', N''' + @Users + '''
|
|
PRINT ''ADD USER [' + @Users + '] ON ROLE [' + @RoleName + ']''
|
|
'
|
|
|
|
FETCH NEXT FROM UsersCurs INTO @Users
|
|
END
|
|
|
|
CLOSE UsersCurs
|
|
DEALLOCATE UsersCurs
|
|
|
|
EXEC sp_executesql @Command
|
|
|
|
|
|
/*=============================================================================
|
|
Drop temp tables
|
|
=============================================================================*/
|
|
DROP TABLE #Securables
|
|
DROP TABLE #UsersOnRole
|
|
|
|
/*=============================================================================
|
|
Script to create / map all users
|
|
=============================================================================*/
|
|
|
|
DECLARE @username varchar(255),
|
|
@Database VARCHAR(255)
|
|
|
|
SET @username = ''
|
|
SET @Command = ''
|
|
SET @Database = ''
|
|
|
|
CREATE TABLE #AllUsersAndRoles(databasename varchar(255), rolename varchar(255), username varchar(255))
|
|
|
|
INSERT INTO #AllUsersAndRoles
|
|
VALUES
|
|
('Arizona','dbRoleDevUsr','AMAVITA\L-AM-AP-SQL-AMA-Pharmacy_Servers_Development'),
|
|
('Arizona','dbRoleDevUsr','CENTRALINFRA\L-CI-AP-SQL-AMA-Pharmacy_Servers_Development'),
|
|
('Arizona','dbRoleDevUsr','CENTRALINFRA\L-CI-AP-SQL-CVI-Pharmacy_Servers_Development'),
|
|
('Arizona','dbRoleDevUsr','CENTRALINFRA\L-CI-AP-SQL-SUN-Pharmacy_Servers_Development'),
|
|
('Arizona','dbRoleDevUsr','COOP-VITALITY\L-CV-AP-SQL-CVI-Pharmacy_Servers_Development'),
|
|
('Arizona','dbRoleDevUsr','SUNSTORE\L-SU-AP-SQL-SUN-Pharmacy_Servers_Development')
|
|
|
|
DECLARE MapUsersAndRolesCurs CURSOR FOR
|
|
SELECT databasename, rolename, username FROM #AllUsersAndRoles AUAR JOIN master.sys.databases D ON D.name = AUAR.databasename
|
|
|
|
OPEN MapUsersAndRolesCurs
|
|
FETCH NEXT FROM MapUsersAndRolesCurs INTO @Database, @rolename, @username
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
SET @Command = 'USE ' + @Database + '
|
|
IF EXISTS (SELECT 1 FROM master.dbo.syslogins WHERE name = ''' + @username + ''')
|
|
BEGIN
|
|
IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N''' + @rolename + ''' AND [type] = ''R'')
|
|
BEGIN
|
|
IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N''' + @username + ''')
|
|
BEGIN
|
|
ALTER USER [' + @username + '] WITH LOGIN = [' + @username + ']
|
|
EXEC sp_addrolemember N''' + @rolename + ''', N''' + @username + '''
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
CREATE USER [' + @username + '] FOR LOGIN [' + @username + '] WITH DEFAULT_SCHEMA=[dbo]
|
|
EXEC sp_addrolemember N''' + @rolename + ''', N''' + @username + '''
|
|
END
|
|
END
|
|
END
|
|
'
|
|
|
|
EXEC sp_executesql @Command
|
|
|
|
FETCH NEXT FROM MapUsersAndRolesCurs INTO @Database, @rolename, @username
|
|
END
|
|
|
|
CLOSE MapUsersAndRolesCurs
|
|
DEALLOCATE MapUsersAndRolesCurs
|
|
|
|
DROP TABLE #AllUsersAndRoles
|
|
GO
|