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