Database security permissions save script

if exists(select 1 from sys.procedures where name = 'script_db_permissions')
drop procedure dbo.script_db_permissions
go

Create Procedure dbo.script_db_permissions
@DBName nvarchar(128),            -- Required: target database from which to pull permissions.
@DBName_Alt nvarchar(128) = null,  -- Optional: Specify this if you want the output USE statement to say a different database name
@UserName nvarchar(128) = null
AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;

print '-- executing script_db_permissions on ' + @DBName

declare @sqltemp nvarchar(MAX)
declare @id INT

set @DBName = ISNULL(@DBName, DB_NAME())

DECLARE @SQL TABLE
(
ID int identity(1, 1) not null primary key,
SQLStatement varchar(MAX)
)

declare @db_principals TABLE
(
name sysname,
principal_id int,
default_schema_name sysname
)

declare @db_role_members TABLE
(
role_name nvarchar(128),
role_principal_id int,
member_principal_id int
)

declare @db_permissions TABLE
(
class tinyint,
class_desc nvarchar(60),
major_id int,
minor_id int,
grantee_principal_id int,
grantor_principal_id int,
type char(4),
permission_name nvarchar(128),
state char(1),
state_desc nvarchar(60)
)

declare @schemas TABLE
(
name nvarchar(128),
schema_id int,
principal_id int
)

declare @objects TABLE
(
name nvarchar(128),
object_id int,
principal_id int,
schema_id int,
schema_name nvarchar(128) -- SCHEMA_NAME(schema_id)
)

declare @columns TABLE
(
object_id int,
name nvarchar(128),
column_id int
)

-- POPULATE THE local SECURITY TABLES -------------------------------------------------------------------------------------
 -- @db_principals
set @sqltemp = 
'USE ' + QUOTENAME(@DBName) + ';select name, principal_id, ISNULL([default_schema_name], ''dbo'')
FROM sys.database_principals
WHERE [type] IN (''U'', ''S'', ''G'') 
and name not like ''##%''
and name not like ''NT SERVICE%''
and name not like ''NT AUTHORITY%''
and name not like ''BUILTIN%''
and principal_id > 4'

IF @UserName is NOT NULL
set @sqltemp = @sqltemp + ' and name like ''' + @UserName + ''''

insert into @db_principals (name, principal_id, default_schema_name) exec sp_executesql @sqltemp

-- @db_role_members
set @sqltemp = 'use ' + QUOTENAME(@DBName) + '; select USER_NAME(role_principal_id), role_principal_id, member_principal_id from sys.database_role_members' 
insert into @db_role_members (role_name, role_principal_id, member_principal_id) exec sp_executesql @sqltemp
delete from @db_role_members where member_principal_id not in (select principal_id from @db_principals)  -- remove unneeded roles

-- db_permissions
set @sqltemp = 'use ' + QUOTENAME(@DBName) + '; select class, class_desc, major_id, minor_id, grantee_principal_id, grantor_principal_id, type, permission_name, state, state_desc from sys.database_permissions'
insert into @db_permissions (class, class_desc, major_id, minor_id, grantee_principal_id, grantor_principal_id, type, permission_name, state, state_desc) exec sp_executesql @sqltemp

-- @schemas
set @sqltemp = 'use ' + QUOTENAME(@DBName) + '; select name, schema_id, principal_id from sys.schemas'
insert into @schemas (name, [schema_id], principal_id) exec sp_executesql @sqltemp

-- @objects
set @sqltemp = 'use ' + QUOTENAME(@DBName) + '; select name, object_id, principal_id, schema_id, SCHEMA_NAME(schema_id) from sys.objects'
insert into @objects (name, [object_id], principal_id, [schema_id], [schema_name]) exec sp_executesql @sqltemp

-- columns
set @sqltemp = 'use ' + QUOTENAME(@DBName) + '; select [object_id], name, column_id from sys.columns'
insert into @columns ([object_id], name, column_id) exec sp_executesql @sqltemp

--select * from @db_principals
--select * from @db_role_members
--select * from @db_permissions
--select * from @schemas
--select * from @objects
--select * from @columns


-- ========= CREATE THE OUTPUT SCRIPT ========================================================================================

-- Insert the USE statement
insert into @SQL(SQLStatement) select 'USE ' + ISNULL(@DBName_Alt, @DBName) + ';'


-- DATABASE PRINCIPALS BLOCK --------------------------------------------------------------------------------
insert into @SQL (SQLStatement) select '-- Logins'

insert into @SQL (SQLStatement)
SELECT  'IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name = ''' + [name] + '''' + 
') BEGIN CREATE USER ' + QUOTENAME(name) + ' FOR LOGIN ' + QUOTENAME(name) + 
' WITH DEFAULT_SCHEMA = ' + QUOTENAME(default_schema_name)
+ ' END; ' 
FROM    @db_principals

-- DATABASE ROLES BLOCK -------------------------------------------------------------------------------------
insert into @SQL (SQLStatement) select '-- database roles'

insert into @SQL (SQLStatement)
SELECT  
'EXEC sp_addrolemember @rolename = ''' + rm.role_name + ''', @membername = ''' + p.name + ''''
FROM    
@db_role_members AS rm join @db_principals p on (rm.member_principal_id = p.principal_id)

-- DB LEVEL EXPLICITLY GRANTED PERMISSIONS BLOCK --------------------------------------------------------------
insert into @SQL (SQLStatement) select '-- explicitly granted DB permissions'

insert into @SQL (SQLStatement)
SELECT  CASE 
        WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
        ELSE 'GRANT'
        END
    + SPACE(1) + perm.permission_name
    + ' TO ' + QUOTENAME(p.name) COLLATE database_default
    + CASE 
            WHEN perm.state <> 'W' THEN SPACE(0) 
            ELSE SPACE(1) + 'WITH GRANT OPTION' 
      END
FROM    
@db_permissions as perm 
join @db_principals as p ON (perm.grantee_principal_id = p.principal_id)
WHERE   
perm.major_id = 0

-- SCHEMA LEVEL PERMISSIONS ---------------------------------------------------------------------------
insert into @SQL (SQLStatement) select '-- schema level permissions'
insert into @SQL (SQLStatement)
SELECT  CASE
WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
ELSE 'GRANT'
END
+ SPACE(1) + perm.permission_name --CONNECT, etc
+ SPACE(1) + 'ON' + SPACE(1) + perm.class_desc + '::' COLLATE database_default
+ QUOTENAME(s.name)
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(p.name) COLLATE database_default
+ CASE
  WHEN perm.state <> 'W' THEN SPACE(0)
  ELSE SPACE(1) + 'WITH GRANT OPTION'
  END
from 
@db_permissions AS perm
    inner join @schemas s on perm.major_id = s.schema_id
    inner join @db_principals p on (perm.grantee_principal_id = p.principal_id)
WHERE perm.class = 3 --class 3 = schema

-- OBJECT LEVEL PERMISSIONS ---------------------------------------------------------------------------------
insert into @SQL (SQLStatement) select '-- object level permissions'
insert into @SQL (SQLStatement)
SELECT  CASE 
        WHEN perm.state <> 'W' THEN perm.state_desc 
        ELSE 'GRANT'
        END
        + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(obj.schema_name) + '.' + QUOTENAME(obj.name)
        + CASE
          WHEN cl.column_id IS NULL THEN SPACE(0)
          ELSE '(' + QUOTENAME(cl.name) + ')'
          END
        + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(p.name) COLLATE database_default
        + CASE 
          WHEN perm.state <> 'W' THEN SPACE(0)
          ELSE SPACE(1) + 'WITH GRANT OPTION'
          END
FROM    
@db_permissions AS perm
    JOIN @objects AS obj ON (perm.major_id = obj.[object_id])
    JOIN @db_principals AS p ON (perm.grantee_principal_id = p.principal_id)
    LEFT JOIN @columns AS cl ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id

-- PRINT OUT GENERATED SCRIPT --------------------------------------------------------------------------------------
select @id = MIN(ID) from @SQL

WHILE @id IS NOT NULL
BEGIN 
select @sqltemp = SQLStatement from @SQL where ID = @id
print @sqltemp
select @id = MIN(ID) from @SQL where ID > @id
END
print ''



USE [dbadmin]
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

IF EXISTS(select name from sys.procedures where name = 'save_permissions')
drop procedure save_permissions
GO

CREATE procedure [dbo].[save_permissions] (@dbname varchar(30))
as
--EaganT 10/1/2013 - Included a Create Security Database is not exists
--Aaron Combs 5/2/2020 - Increased the number of characers for @StrSQL and @cmd1

--declare @dbname varchar(30)
declare @StrSQL varchar(max)
declare @dt varchar(10)
declare @security_db varchar(50)
declare @cmd1 varchar(max)

set @security_db =  LEFT(REPLACE(@@SERVERNAME,'-','')+'',(CHARINDEX('',REPLACE(@@SERVERNAME+'','-','')))-1)+'_SECURITY' 
If not exists(Select 1 from master..sysdatabases where name = @security_db)
begin
set @cmd1 = 'Create Database '+@security_db
exec (@cmd1)
end

--set @dbname = 'qcsidb'
if exists (select * from master..sysdatabases where name like @dbname) begin
set @dt =  (convert(varchar(8),getdate(),112))
--set @security_db = upper(left(@dbname,patindex('%[0-9]%',@dbname)))+'_SECURITY'
set @StrSQL ='if exists (select * from '+@security_db+'..sysobjects where name like '''+@dbname+'_role_members_'+@dt+''') drop table '+@security_db+'..'+@dbname+'_role_members_'+@dt
print @StrSQL
exec(@StrSQL)

set @StrSQL ='if exists (select * from '+@security_db+'..sysobjects where name like '''+@dbname+'_principals_'+@dt+''') drop table '+@security_db+'..'+@dbname+'_principals_'+@dt
print @StrSQL
exec(@StrSQL)

set @StrSQL ='if exists (select * from '+@security_db+'..sysobjects where name like '''+@dbname+'_permissions_'+@dt+''') drop table '+@security_db+'..'+@dbname+'_permissions_'+@dt
print @StrSQL
exec(@StrSQL)

set @StrSQL ='if exists (select * from '+@security_db+'..sysobjects where name like '''+@dbname+'_roleperm_'+@dt+''') drop table '+@security_db+'..'+@dbname+'_roleperm_'+@dt
print @StrSQL
exec(@StrSQL)

set @StrSQL ='select * into '+@security_db+'..'+@dbname+'_role_members_'+@dt+' from '+@dbname+'.sys.database_role_members'
print @StrSQL
exec(@StrSQL)

set @StrSQL ='select * into '+@security_db+'..'+@dbname+'_principals_'+@dt+' from '+@dbname+'.sys.database_principals'
print @StrSQL
exec(@StrSQL)

set @StrSQL ='select * into '+@security_db+'..'+@dbname+'_permissions_'+@dt+' from '+@dbname+'.sys.database_permissions'
print @StrSQL
exec(@StrSQL)

End

else print 'This db does not exist on this server'

USE [dbadmin]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

IF EXISTS(select name from sys.procedures where name = 'tsp_save_permissions_nightly')
drop procedure save_permissions_nightlyGO

CREATE procedure [dbo].[tsp_save_permissions_nightly] (@dbname varchar(30))
as
BEGIN
declare @StrSQL nvarchar(max)
declare @dt varchar(10) = (convert(varchar(8),getdate(),112))
declare @security_db sysname = CONVERT(varchar(128),SERVERPROPERTY('ComputerNamePhysicalNetBIOS')) + '_SECURITY'

set @dbname = LTRIM(RTRIM(@dbname))

IF exists (select * from master..sysdatabases where name like @dbname)
BEGIN
print ''
print @dbname
-- Drop existing tables
set @StrSQL ='if exists (select * from '+@security_db+'.sys.tables where name = ''' + @dbname +'_role_members_nightly_'+@dt+''') drop table ' + @security_db+'.dbo.'+@dbname + '_role_members_nightly_' + @dt
print @StrSQL
exec sp_executesql @StrSQL

set @StrSQL ='if exists (select * from '+@security_db+'.sys.tables where name = ''' + @dbname +'_principals_nightly_'+@dt+''') drop table ' + @security_db+'.dbo.'+@dbname + '_principals_nightly_' + @dt
print @StrSQL
exec sp_executesql @StrSQL

set @StrSQL ='if exists (select * from '+@security_db+'.sys.tables where name = ''' + @dbname +'_permissions_nightly_'+@dt+''') drop table ' + @security_db+'.dbo.'+@dbname + '_permissions_nightly_' + @dt
print @StrSQL
exec sp_executesql @StrSQL

set @StrSQL ='if exists (select * from '+@security_db+'.sys.tables where name = ''' + @dbname +'_roleperm_nightly_'+@dt+''') drop table ' + @security_db+'.dbo.'+@dbname + '_roleperm_nightly_' + @dt
print @StrSQL
exec sp_executesql @StrSQL

-- Save Permissions
set @StrSQL ='select * into '+@security_db+'..'+@dbname+'_role_members_nightly_'+@dt+' from '+@dbname+'.sys.database_role_members'
print @StrSQL
exec sp_executesql @StrSQL

set @StrSQL ='select * into '+@security_db+'..'+@dbname+'_principals_nightly_'+@dt+' from '+@dbname+'.sys.database_principals'
print @StrSQL
exec sp_executesql @StrSQL

set @StrSQL ='select * into '+@security_db+'..'+@dbname+'_permissions_nightly_'+@dt+' from '+@dbname+'.sys.database_permissions'
print @StrSQL
exec sp_executesql @StrSQL


set @StrSQL = 'SELECT '''+ 
@dbname +
''' DBName,convert(varchar(20),name) DB_ROLE, convert(varchar(20),permission_name) PERMISSION into ' + 
@security_db +'..'+@dbname + '_roleperm_nightly_'+ @dt +
' FROM '+ @dbname + '.sys.database_permissions AS perm INNER JOIN '+ 
@dbname + '.sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id WHERE usr.name IN (SELECT name FROM  ' +
@dbname + '.sys.database_principals WHERE type_desc =''DATABASE_ROLE'')'
print (@StrSQL)
exec sp_executesql @StrSQL
END
END

use dbadmin
go

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

IF EXISTS(select name from sys.procedures where name = 'restore_permissions_print')
drop procedure restore_permissions_printGO

CREATE procedure [dbo].[tsp_restore_permissions_print] (@dbname varchar(30))      
as      
SET NOCOUNT ON    
--AKrehbiel Modified 07/07/09 to check for previous day's security backups (restore process spans midnight)
--also added an underscore to the end of the search string for db names to prevent from
--pulling all xc, custom, stage, etc when the core db is entered.
     
--declare @dbname varchar(30)      
declare @StrSQL varchar(2000)      
declare @dt varchar(10)      
declare @security_db varchar(30)      
declare @env varchar(10)      
declare @command varchar(2000)      
declare @unique varchar(10) --name of temp db that houses the script to be executed.      
declare @loop int      
      
--set @dbname = 'facchtst0xc'      
set @dt =  (convert(varchar(8),getdate(),112))      
set @security_db = LEFT(REPLACE(@@SERVERNAME,'-','')+'',(CHARINDEX('',REPLACE(@@SERVERNAME+'','-','')))-1)+'_SECURITY'       
set @unique = right(convert(varchar(20),getdate(),112),4)+replace((convert(varchar(20),getdate(),108)),':','')      
      
--drop ##SECURITY_TABLES table      
if exists (SELECT * FROM tempdb..sysobjects where name like '##SECURITY_TABLES') drop table ##SECURITY_TABLES      
      
set @StrSQL= 'select name into ##SECURITY_TABLES from '+@security_db+'..sysobjects where type like ''U'' and name like '''+@dbname+'[_]%'+@dt+''''      
exec(@StrSQL)      

--account for restore process spanning midnight
--check for previous day's records
if (select count(*) from ##SECURITY_TABLES) < 3 
begin
set @dt =  (convert(varchar(8),getdate()-1,112))   

if exists (SELECT * FROM tempdb..sysobjects where name like '##SECURITY_TABLES') drop table ##SECURITY_TABLES      
      
set @StrSQL= 'select name into ##SECURITY_TABLES from '+@security_db+'..sysobjects where type like ''U'' and name like '''+@dbname+'[_]%'+@dt+''''      
exec(@StrSQL) 
end

--if still no records, err out.      
if (select count(*) from ##SECURITY_TABLES) < 3 begin     
--following query will fail if no security tables exist - Tidal will register failure
set @StrSQL = 'select * into #nulltable from '+@security_db+'..'+@dbname+'_role_members_'+@dt
exec(@StrSQL)

goto nosecurity 
end

-- drop schema script - ##<dbname>_dropschema      
set @StrSQL = 'if exists (select * from tempdb..sysobjects where name like ''##'+@dbname      
+'_dropschema%'') drop table ##'+@dbname      
+'_dropschema'      
--print @StrSQL      
exec(@StrSQL)      
      
-- drop schema script - ##<dbname>_dropschema      
set @StrSQL= 'select ''use '+@dbname      
+';drop schema [''+ s.name +''];'' as command into ##'+@dbname      
+'_dropschema from '+@dbname      
+'.sys.schemas s, '+@dbname+'.sys.database_principals p       
where s.schema_id > 4      
and p.type in (''S'',''R'',''U'',''G'')      
and p.is_fixed_role != 1      
and p.principal_id = s.principal_id and s.name not like ''audit'''      
--print @StrSQL      
exec(@StrSQL)      
       
-- drop user script - ##<dbname>_dropuser      
set @StrSQL = 'if exists (select * from tempdb..sysobjects where name like ''##'+@dbname      
+'_dropuser%'') drop table ##'+@dbname      
+'_dropuser'      
--print @StrSQL      
exec(@StrSQL)      
      
set @StrSQL = 'select ''use '+@dbname      
+'; drop user [''+ name +''];'' as command into ##'+@dbname      
+'_dropuser from '+@dbname      
+'.sys.database_principals where principal_id > 4 and type in (''S'',''U'',''G'') and name not like ''audit'''      
--print @StrSQL      
exec(@StrSQL)      
      
-- drop role member script - ##<dbname>_droprolemember      
set @StrSQL = 'if exists (select * from tempdb..sysobjects where name like ''##'+@dbname      
+'_droprolemember%'') drop table ##'+@dbname      
+'_droprolemember'      
--print @StrSQL      
exec(@StrSQL)      
      
set @StrSQL = 'select ''exec '      
+@dbname+'..sp_droprolemember ''''''+r.name+'''''',''''''+u.name      
+'''''';'' as command into ##'+@dbname      
+'_droprolemember       
from '+@dbname+'.sys.database_role_members m,       
'+@dbname+'.sys.database_principals u,       
'+@dbname+'.sys.database_principals r      
where member_principal_id > 4      
and m.role_principal_id = r.principal_id      
and m.member_principal_id = u.principal_id'      
--print @StrSQL      
exec(@StrSQL)      
      
-- drop role script - ##<dbname>_droprole      
set @StrSQL = 'if exists (select * from tempdb..sysobjects where name like ''##'+@dbname      
+'_droprole%'' and name not like ''%member%'') drop table ##'+@dbname      
+'_droprole'      
--print @StrSQL      
exec(@StrSQL)      
      
set @StrSQL = 'select ''use '+@dbname      
+'; drop role ''+ name +'';'' as command into ##'+@dbname      
+'_droprole from '+@dbname      
+'.sys.database_principals where type = ''R'' and name not like ''public'' and is_fixed_role = 0'      
--print @StrSQL      
exec(@StrSQL)      
      
-- create user script - ##<dbname>_createuser      
set @StrSQL = 'if exists (select * from tempdb..sysobjects where name like ''##'+@dbname      
+'_createuser%'') drop table ##'+@dbname      
+'_createuser'      
--print @StrSQL      
exec(@StrSQL)      
      
set @StrSQL = 'select ''use '+@dbname      
+'; if exists (select name from master.sys.syslogins where name like ''''''+ name+'''''') create user [''+ name+''] for login [''+ name+''] with default_schema=[dbo];'' as command into ##'+@dbname      
+'_createuser from '+@security_db+'..'+@dbname      
+'_principals_'+@dt+' where principal_id > 4 and type in (''S'',''U'') and name not like ''audit'''      
--print @StrSQL      
exec(@StrSQL)   

-- grant NT user access - ##<dbname>_grantdomainuser
set @StrSQL = 'if exists (select * from tempdb..sysobjects where name like ''##'+@dbname      
+'_grantdomainuser%'') drop table ##'+@dbname      
+'_grantdomainuser'      
--print @StrSQL      
exec(@StrSQL)      
 
set @StrSQL = 'select ''use '+@dbname      
+'; if exists (select name from master.sys.syslogins where name like ''''''+ name+'''''') exec sp_grantdbaccess [''+ name+'']'' as command into ##'+@dbname      
+'_grantdomainuser from '+@security_db+'..'+@dbname      
+'_principals_'+@dt+' where principal_id > 4 and type in (''G'') '
--print @StrSQL      
exec(@StrSQL)  
      
-- create role script - ##<dbname>_createrole      
set @StrSQL = 'if exists (select * from tempdb..sysobjects where name like ''##'+@dbname      
+'_createrole%'') drop table ##'+@dbname      
+'_createrole'      
--print @StrSQL      
exec(@StrSQL)      
      
set @StrSQL = 'select ''use '+@dbname      
+'; create role [''+ name+''];'' as command into ##'+@dbname      
+'_createrole from '+@security_db+'..'+@dbname+'_principals_'+@dt+' where type = ''R'' and name not like ''public'' and is_fixed_role = 0'      
--print @StrSQL      
exec(@StrSQL)      
      
-- add role member script - ##<dbname>_addrolemember      
set @StrSQL = 'if exists (select * from tempdb..sysobjects where name like ''##'+@dbname      
+'_addrolemember%'') drop table ##'+@dbname      
+'_addrolemember'      
--print @StrSQL      
exec(@StrSQL)      
      
set @StrSQL = 'select ''if exists (select name from '+@dbname+'..sysusers where name like ''''''+u.name+'''''') exec '      
+@dbname+'..sp_addrolemember ''''''+r.name+'''''',''''''+u.name      
+'''''';'' as command into ##'+@dbname      
+'_addrolemember       
from '+@security_db+'..'+@dbname+'_role_members_'+@dt+' m, '+@security_db+'..'+@dbname+'_principals_'+@dt+' u, '      
+@security_db+'..'+@dbname+'_principals_'+@dt+' r      
where m.role_principal_id = r.principal_id      
and m.member_principal_id = u.principal_id      
and u.principal_id > 4'      
--print @StrSQL      
exec(@StrSQL)    

--grant permissions
set @StrSQL = 'if exists (select * from tempdb..sysobjects where name like ''##'+@dbname      
+'_grantobjectpermissions%'') drop table ##'+@dbname      
+'_grantobjectpermissions'      
--print @StrSQL      
exec(@StrSQL)      

set @StrSQL = 'select ''use '+@dbname
+'; grant ''+p.permission_name+'' on ''+o.name+'' to [''+u.name+'']'' COLLATE DATABASE_DEFAULT as command into ##'+@dbname      
+'_grantobjectpermissions from '+@security_db+'..'+@dbname+'_permissions_'+@dt+' p, '+@security_db+'..'+@dbname+'_principals_'+@dt+' u
, '+@dbname+'..sysobjects o 
WHERE p.grantee_principal_id > 4
and o.id = p.major_id
and major_id != 0
and p.grantee_principal_id = u.principal_id 
AND p.permission_name NOT LIKE ''CONNECT'''

--print @StrSQL
exec(@StrSQL)

set @StrSQL = 'if exists (select * from tempdb..sysobjects where name like ''##'+@dbname      
+'_grantdbpermissions%'') drop table ##'+@dbname      
+'_grantdbpermissions'      
--print @StrSQL      
exec(@StrSQL)      

set @StrSQL = 'select ''use '+@dbname
+'; grant ''+permission_name+'' to [''+u.name+'']'' COLLATE DATABASE_DEFAULT as command into ##'+@dbname      
+'_grantdbpermissions from '+@security_db+'..'+@dbname+'_permissions_'+@dt+' p, '+@security_db+'..'+@dbname+'_principals_'+@dt+' u
WHERE p.grantee_principal_id > 4
and p.class_desc like ''DATABASE''
and p.grantee_principal_id = u.principal_id 
AND p.permission_name NOT LIKE ''CONNECT'''

--print @StrSQL
exec(@StrSQL)

--create the table that will house all of the commands:      
set @StrSQL = 'create table dbadmin..tzg_restore_security_'+@unique+' (id int IDENTITY, command varchar(1000))'      
--print @StrSQL      
exec(@StrSQL)      
      
set @StrSQL = 'insert into dbadmin..tzg_restore_security_'+@unique+' select command from ##'+@dbname+'_droprolemember'      
--print @StrSQL      
exec (@StrSQL)      
      
set @StrSQL = 'insert into dbadmin..tzg_restore_security_'+@unique+' select command from ##'+@dbname+'_dropschema'      
--print @StrSQL      
exec (@StrSQL)      
      
set @StrSQL = 'insert into dbadmin..tzg_restore_security_'+@unique+' select command from ##'+@dbname+'_dropuser'      
--print @StrSQL      
exec (@StrSQL)      
      
set @StrSQL = 'insert into dbadmin..tzg_restore_security_'+@unique+' select command from ##'+@dbname+'_droprole'      
--print @StrSQL      
exec (@StrSQL)      
      
set @StrSQL = 'insert into dbadmin..tzg_restore_security_'+@unique+' select command from ##'+@dbname+'_createuser'      
--print @StrSQL      
exec (@StrSQL)      

set @StrSQL = 'insert into dbadmin..tzg_restore_security_'+@unique+' select command from ##'+@dbname+'_grantdomainuser'      
--print @StrSQL      
exec (@StrSQL)      
      
set @StrSQL = 'insert into dbadmin..tzg_restore_security_'+@unique+' select command from ##'+@dbname+'_createrole'      
--print @StrSQL      
exec (@StrSQL)      
      
set @StrSQL = 'insert into dbadmin..tzg_restore_security_'+@unique+' select command from ##'+@dbname+'_addrolemember'      
--print @StrSQL      
exec (@StrSQL)      

set @StrSQL = 'insert into dbadmin..tzg_restore_security_'+@unique+' select command from ##'+@dbname+'_grantobjectpermissions'      
--print @StrSQL      
exec (@StrSQL)      

set @StrSQL = 'insert into dbadmin..tzg_restore_security_'+@unique+' select command from ##'+@dbname+'_grantdbpermissions'      
--print @StrSQL      
exec (@StrSQL)      

      
set @StrSQL = 'declare @loop int      
declare @command varchar(1000)      
declare @loopcount int      
print ''--*********  RUN SCRIPT ON TARGET DB  ***************''    
PRINT ''''    
select @loop = 1      
select @loopcount = max(id) from tzg_restore_security_'+@unique+'      
select @command = command from tzg_restore_security_'+@unique+' where id = @loop      
while @loop <= @loopcount begin      
  select @command = command from tzg_restore_security_'+@unique+' where id = @loop      
  print(@command)      
  --exec(@command)      
  select @loop = min(id) from tzg_restore_security_'+@unique+' where id>@loop      
  end'      
--print @StrSQL      
exec(@StrSQL)      
  
/*      
set @StrSQL = 'exec '+@dbname+'..tsp_grant_onrefresh'      
print @StrSQL      
--exec(@StrSQL)      
*/  
     
goto endproc      
      
nosecurity:      
print '#####   ERROR   #####
No '+@dbname+' security tables exist for today      
check '+@security_db+' to determine what tables are available.'      
      
endproc:      
  

GO

USE [dbadmin]
GO

IF EXISTS(select name from sys.procedures where name = 'restore_permissions')
drop procedure restore_permissions
GO

CREATE procedure [dbo].[restore_permissions] (@dbname varchar(30), @print_only bit=0)    
as      
SET NOCOUNT ON    
--AKrehbiel Modified 07/07/09 to check for previous day's security backups (restore process spans midnight)
--also added an underscore to the end of the search string for db names to prevent from
--pulling all xc, custom, stage, etc when the core db is entered.
-- Updated on 2020-05-02 by Aaron Combs --- Re-wrote execution phase to use error handling.  
     
--declare @dbname varchar(30)      
declare @StrSQL varchar(2000)      
declare @dt varchar(10)      
declare @security_db varchar(30)      
declare @env varchar(10)      
declare @command varchar(2000)      
declare @unique varchar(10) --name of temp db that houses the script to be executed.      
declare @loop int      
      
set @dt =  (convert(varchar(8),getdate(),112))      
set @security_db = LEFT(REPLACE(@@SERVERNAME,'-','')+'',(CHARINDEX('',REPLACE(@@SERVERNAME+'','-','')))-1)+'_SECURITY'      
set @unique = right(convert(varchar(20),getdate(),112),4)+replace((convert(varchar(20),getdate(),108)),':','')      

      
--drop ##SECURITY_TABLES table      
if exists (SELECT * FROM tempdb..sysobjects where name like '##SECURITY_TABLES') drop table ##SECURITY_TABLES      
      
set @StrSQL= 'select name into ##SECURITY_TABLES from '+@security_db+'..sysobjects where type like ''U'' and name like '''+@dbname+'[_]%'+@dt+''''      
exec(@StrSQL)      

--account for restore process spanning midnight
--check for previous day's records
if (select count(*) from ##SECURITY_TABLES) < 3 
begin
set @dt =  (convert(varchar(8),getdate()-1,112))   

if exists (SELECT * FROM tempdb..sysobjects where name like '##SECURITY_TABLES') drop table ##SECURITY_TABLES      
      
set @StrSQL= 'select name into ##SECURITY_TABLES from '+@security_db+'..sysobjects where type like ''U'' and name like '''+@dbname+'[_]%'+@dt+''''      
exec(@StrSQL) 
end

--if still no records, err out.      
if (select count(*) from ##SECURITY_TABLES) < 3 begin     
--following query will fail if no security tables exist - Tidal will register failure
set @StrSQL = 'select * into #nulltable from '+@security_db+'..'+@dbname+'_role_members_'+@dt
exec(@StrSQL)

goto nosecurity 
end

-- drop schema script - ##<dbname>_dropschema      
set @StrSQL = 'if exists (select * from tempdb..sysobjects where name like ''##'+@dbname      
+'_dropschema%'') drop table ##'+@dbname      
+'_dropschema'      
--print @StrSQL      
exec(@StrSQL)      
      
-- drop schema script - ##<dbname>_dropschema      
set @StrSQL= 'select ''use '+@dbname      
+';drop schema [''+ s.name +''];'' as command into ##'+@dbname      
+'_dropschema from '+@dbname      
+'.sys.schemas s, '+@dbname+'.sys.database_principals p       
where s.schema_id > 4      
and p.type in (''S'',''R'',''U'',''G'')      
and p.is_fixed_role != 1      
and p.principal_id = s.principal_id and s.name not like ''audit'''      
--print @StrSQL      
exec(@StrSQL)      
       
-- drop user script - ##<dbname>_dropuser      
set @StrSQL = 'if exists (select * from tempdb..sysobjects where name like ''##'+@dbname      
+'_dropuser%'') drop table ##'+@dbname      
+'_dropuser'      
--print @StrSQL      
exec(@StrSQL)      
      
set @StrSQL = 'select ''use '+@dbname      
+'; drop user [''+ name +''];'' as command into ##'+@dbname      
+'_dropuser from '+@dbname      
+'.sys.database_principals where principal_id > 4 and type in (''S'',''U'',''G'') and name not like ''audit'''      
--print @StrSQL      
exec(@StrSQL)      
      
-- drop role member script - ##<dbname>_droprolemember      
set @StrSQL = 'if exists (select * from tempdb..sysobjects where name like ''##'+@dbname      
+'_droprolemember%'') drop table ##'+@dbname      
+'_droprolemember'      
--print @StrSQL      
exec(@StrSQL)      
      
set @StrSQL = 'select ''exec '      
+@dbname+'..sp_droprolemember ''''''+r.name+'''''',''''''+u.name      
+'''''';'' as command into ##'+@dbname      
+'_droprolemember       
from '+@dbname+'.sys.database_role_members m,       
'+@dbname+'.sys.database_principals u,       
'+@dbname+'.sys.database_principals r      
where member_principal_id > 4      
and m.role_principal_id = r.principal_id      
and m.member_principal_id = u.principal_id'      
--print @StrSQL      
exec(@StrSQL)      
      
-- drop role script - ##<dbname>_droprole      
set @StrSQL = 'if exists (select * from tempdb..sysobjects where name like ''##'+@dbname      
+'_droprole%'' and name not like ''%member%'') drop table ##'+@dbname      
+'_droprole'      
--print @StrSQL      
exec(@StrSQL)      
      
set @StrSQL = 'select ''use '+@dbname      
+'; drop role ''+ name +'';'' as command into ##'+@dbname      
+'_droprole from '+@dbname      
+'.sys.database_principals where type = ''R'' and name not like ''public'' and is_fixed_role = 0'      
--print @StrSQL      
exec(@StrSQL)      
      
-- create user script - ##<dbname>_createuser      
set @StrSQL = 'if exists (select * from tempdb..sysobjects where name like ''##'+@dbname      
+'_createuser%'') drop table ##'+@dbname      
+'_createuser'      
--print @StrSQL      
exec(@StrSQL)      
      
set @StrSQL = 'select ''use '+@dbname      
+'; if exists (select name from master.sys.syslogins where name like ''''''+ name+'''''') create user [''+ name+''] for login [''+ name+''] with default_schema=[dbo];'' as command into ##'+@dbname      
+'_createuser from '+@security_db+'..'+@dbname      
+'_principals_'+@dt+' where principal_id > 4 and type in (''S'',''U'') and name not like ''audit'''      
--print @StrSQL      
exec(@StrSQL)   

-- grant NT user access - ##<dbname>_grantdomainuser
set @StrSQL = 'if exists (select * from tempdb..sysobjects where name like ''##'+@dbname      
+'_grantdomainuser%'') drop table ##'+@dbname      
+'_grantdomainuser'      
--print @StrSQL      
exec(@StrSQL)      
 
set @StrSQL = 'select ''use '+@dbname      
+'; if exists (select name from master.sys.syslogins where name like ''''''+ name+'''''') exec sp_grantdbaccess [''+ name+'']'' as command into ##'+@dbname      
+'_grantdomainuser from '+@security_db+'..'+@dbname      
+'_principals_'+@dt+' where principal_id > 4 and type in (''G'') '
--print @StrSQL      
exec(@StrSQL)  
      
-- create role script - ##<dbname>_createrole      
set @StrSQL = 'if exists (select * from tempdb..sysobjects where name like ''##'+@dbname      
+'_createrole%'') drop table ##'+@dbname      
+'_createrole'      
--print @StrSQL      
exec(@StrSQL)      
      
set @StrSQL = 'select ''use '+@dbname      
+'; create role [''+ name+''];'' as command into ##'+@dbname      
+'_createrole from '+@security_db+'..'+@dbname+'_principals_'+@dt+' where type = ''R'' and name not like ''public'' and is_fixed_role = 0'      
--print @StrSQL      
exec(@StrSQL)      
      
-- add role member script - ##<dbname>_addrolemember      
set @StrSQL = 'if exists (select * from tempdb..sysobjects where name like ''##'+@dbname      
+'_addrolemember%'') drop table ##'+@dbname      
+'_addrolemember'      
--print @StrSQL      
exec(@StrSQL)      
      
set @StrSQL = 'select ''if exists (select name from '+@dbname+'..sysusers where name like ''''''+u.name+'''''') exec '      
+@dbname+'..sp_addrolemember ''''''+r.name+'''''',''''''+u.name      
+'''''';'' as command into ##'+@dbname      
+'_addrolemember       
from '+@security_db+'..'+@dbname+'_role_members_'+@dt+' m, '+@security_db+'..'+@dbname+'_principals_'+@dt+' u, '      
+@security_db+'..'+@dbname+'_principals_'+@dt+' r      
where m.role_principal_id = r.principal_id      
and m.member_principal_id = u.principal_id      
and u.principal_id > 4'      
--print @StrSQL      
exec(@StrSQL)    

--grant permissions
set @StrSQL = 'if exists (select * from tempdb..sysobjects where name like ''##'+@dbname      
+'_grantobjectpermissions%'') drop table ##'+@dbname      
+'_grantobjectpermissions'      
--print @StrSQL      
exec(@StrSQL)      

set @StrSQL = 'select ''use '+@dbname
+'; grant ''+p.permission_name+'' on ''+o.name+'' to [''+u.name+'']'' COLLATE DATABASE_DEFAULT as command into ##'+@dbname      
+'_grantobjectpermissions from '+@security_db+'..'+@dbname+'_permissions_'+@dt+' p, '+@security_db+'..'+@dbname+'_principals_'+@dt+' u
, '+@dbname+'..sysobjects o 
WHERE p.grantee_principal_id > 4
and o.id = p.major_id
and major_id != 0
and p.grantee_principal_id = u.principal_id 
AND p.permission_name NOT LIKE ''CONNECT'''

--print @StrSQL
exec(@StrSQL)

set @StrSQL = 'if exists (select * from tempdb..sysobjects where name like ''##'+@dbname      
+'_grantdbpermissions%'') drop table ##'+@dbname      
+'_grantdbpermissions'      
--print @StrSQL      
exec(@StrSQL)      

set @StrSQL = 'select ''use '+@dbname
+'; grant ''+permission_name+'' to [''+u.name+'']'' COLLATE DATABASE_DEFAULT as command into ##'+@dbname      
+'_grantdbpermissions from '+@security_db+'..'+@dbname+'_permissions_'+@dt+' p, '+@security_db+'..'+@dbname+'_principals_'+@dt+' u
WHERE p.grantee_principal_id > 4
and p.class_desc like ''DATABASE''
and p.grantee_principal_id = u.principal_id 
AND p.permission_name NOT LIKE ''CONNECT'''

--print @StrSQL
exec(@StrSQL)

--create the table that will house all of the commands:      
set @StrSQL = 'create table dbadmin..tzg_restore_security_'+@unique+' (id int IDENTITY, command varchar(1000))'      
--print @StrSQL      
exec(@StrSQL)      

set @StrSQL = 'insert into dbadmin..tzg_restore_security_'+@unique+' select command from ##'+@dbname+'_droprolemember'      
--print @StrSQL      
exec (@StrSQL)      
      
set @StrSQL = 'insert into dbadmin..tzg_restore_security_'+@unique+' select command from ##'+@dbname+'_dropschema'      
--print @StrSQL      
exec (@StrSQL)      
      
set @StrSQL = 'insert into dbadmin..tzg_restore_security_'+@unique+' select command from ##'+@dbname+'_dropuser'      
--print @StrSQL      
exec (@StrSQL)      
      
set @StrSQL = 'insert into dbadmin..tzg_restore_security_'+@unique+' select command from ##'+@dbname+'_droprole'      
--print @StrSQL      
exec (@StrSQL)      


set @StrSQL = 'insert into dbadmin..tzg_restore_security_'+@unique+' select command from ##'+@dbname+'_createuser'      
--print @StrSQL      
exec (@StrSQL)      

set @StrSQL = 'insert into dbadmin..tzg_restore_security_'+@unique+' select command from ##'+@dbname+'_grantdomainuser'      
--print @StrSQL      
exec (@StrSQL)      
      
set @StrSQL = 'insert into dbadmin..tzg_restore_security_'+@unique+' select command from ##'+@dbname+'_createrole'      
--print @StrSQL      
exec (@StrSQL)      
      
set @StrSQL = 'insert into dbadmin..tzg_restore_security_'+@unique+' select command from ##'+@dbname+'_addrolemember'      
--print @StrSQL      
exec (@StrSQL)      

set @StrSQL = 'insert into dbadmin..tzg_restore_security_'+@unique+' select command from ##'+@dbname+'_grantobjectpermissions'      
--print @StrSQL      
exec (@StrSQL)      

set @StrSQL = 'insert into dbadmin..tzg_restore_security_'+@unique+' select command from ##'+@dbname+'_grantdbpermissions'      
--print @StrSQL      
exec (@StrSQL)      

-- Added by Aaron Combs 2020-05-02 --- Error handling added
-- Create the command queue table to process the commands
create table #cmdqueue  (id int, command nvarchar(max))
set @StrSQL = 'insert into #cmdqueue select id, command from dbadmin..tzg_restore_security_'+@unique
exec (@StrSQL)

--select * from #cmdqueue
declare @i int = 1
declare @maxid int
declare @cmd nvarchar(max)

select @maxid = max(id) from #cmdqueue

WHILE (@i <= @maxid)
BEGIN
select @cmd = command from #cmdqueue where id = @i
IF @print_only = 0
BEGIN TRY
exec sp_executesql @cmd
END TRY
BEGIN CATCH
print ERROR_MESSAGE()
END CATCH
ELSE
print @cmd
set @i = @i + 1
END

drop table #cmdqueue

     
goto endproc      
      
nosecurity:      
print '#####   ERROR   #####
No '+@dbname+' security tables exist for today      
check '+@security_db+' to determine what tables are available.'      
      
endproc:




GO


use dbadmin
GO

SET NOCOUNT ON;

if exists(select name from sys.procedures where name = 'delete_permission_tables')
drop procedure delete_permission_tables
GO

CREATE PROCEDURE delete_permission_tables
as
BEGIN
declare @security_db sysname = CONVERT(varchar(128),SERVERPROPERTY('ComputerNamePhysicalNetBIOS')) + '_SECURITY'
declare @sql nvarchar(max)
declare @table sysname

IF (OBJECT_ID('tempdb..#tables')) IS NOT NULL
drop table #tables
create table #tables (name sysname)

-- Remove old tables from the security database
print 'Removing security tables that are over 3 months old from dbadmin and ' + @security_db
set @sql = 'insert into #tables select ''' + @security_db + '.dbo.'' + name from ' + @security_db + '.sys.tables where create_date <= DATEADD(MONTH, -3, GETDATE())'
exec sp_executesql @sql

-- Include old tables from dbadmin
set @sql = 'insert into #tables select ''dbadmin.dbo.'' + name from dbadmin.sys.tables where name like ''tzg_restore_security%'' and create_date <= DATEADD(MONTH, -3, GETDATE())'
exec sp_executesql @sql

select @table = MIN(name) from #tables
WHILE(@table is not null)
BEGIN
set @sql = 'drop table ' + @table
print @sql
exec sp_executesql @sql
select @table = MIN(name) from #tables where name > @table
END
drop table #tables
END


USE [msdb]
GO

IF EXISTS(select name from msdb.dbo.sysjobs where name = 'Admin - Save Permissions Nightly')
EXEC msdb.dbo.sp_delete_job @job_name=N'Admin - Save Permissions Nightly', @delete_unused_schedule=1
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Admin - Save Permissions Nightly', 
@enabled=1, 
@notify_level_eventlog=0, 
@notify_level_email=0, 
@notify_level_netsend=0, 
@notify_level_page=0, 
@delete_level=0, 
@description=N'Job has been updated to both delete old security tables and create new security tables', 
@category_name=N'[Uncategorized (Local)]', 
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback



EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CreateSecurityDatabase', 
@step_id=1, 
@cmdexec_success_code=0, 
@on_success_action=3, 
@on_success_step_id=0, 
@on_fail_action=2, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'TSQL', 
@command=N'-- Create the security database if it doesn''t exist

declare @security_db sysname = CONVERT(varchar(128),SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')) + ''_SECURITY''
declare @sql nvarchar(max)
IF not exists(select name from sys.databases where name = @security_db)
BEGIN
print ''Creating missing security database '' + @security_db
set @sql = ''create database '' + @security_db
exec sp_executesql @sql
END', 
@database_name=N'master', 
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DeleteOldSecurityTables', 
@step_id=2, 
@cmdexec_success_code=0, 
@on_success_action=3, 
@on_success_step_id=0, 
@on_fail_action=3, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'TSQL', 
@command=N'exec dbadmin..delete_permission_tables', 
@database_name=N'dbadmin', 
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'SavePermissions', 
@step_id=3, 
@cmdexec_success_code=0, 
@on_success_action=1, 
@on_success_step_id=0, 
@on_fail_action=2, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'TSQL', 
@command=N'declare @dbname sysname
declare @sql nvarchar(max)

select @dbname = MIN(name) from sys.databases where database_id > 4 and name not like ''%SECURITY'' and state_desc = ''ONLINE''
WHILE(@dbname is not null)
BEGIN
exec dbadmin.dbo.save_permissions_nightly @dbname
select @dbname = MIN(name) from sys.databases where database_id > 4 and name not like ''%SECURITY'' and state_desc = ''ONLINE'' and name > @dbname
END
', 
@database_name=N'dbadmin', 
@flags=4


IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'daily', 
@enabled=1, 
@freq_type=4, 
@freq_interval=1, 
@freq_subday_type=1, 
@freq_subday_interval=0, 
@freq_relative_interval=0, 
@freq_recurrence_factor=0, 
@active_start_date=20130402, 
@active_end_date=99991231, 
@active_start_time=220000, 
@active_end_time=235959, 
@schedule_uid=N'f82c04a7-0154-487a-b3d5-bee2fbb598e1'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO