USE [CreditDataWarehouse]
GO
/*
**
** Desc: Adds/Resets Master key password to prod database
**
** Auth : Olafsson
**
** Date: 6-May-2019
** Change History
** PR Date Author Description
** — ——– ——- ————————————
**
*/
IF OBJECT_ID(‘dbo.C0012433944’, ‘U’) IS NOT NULL
BEGIN
DROP TABLE dbo.C0012433944
PRINT ‘<<< Success : Dropped dbo.C0012433944 >>>’
END
GO
DECLARE @PASSWORD VARCHAR(128) = CONVERT(VARCHAR(128),CRYPT_GEN_RANDOM(128),1)
SELECT @PASSWORD AS secret, @@SERVERNAME AS servername INTO dbo.C0012433944
DECLARE @SQL VARCHAR(255) = ‘ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = ”’ + @PASSWORD + ””
Exec(@SQL)
PRINT ‘EXECUTED ALTER MASTER KEY ENCRYPTION’
GO
— Revoke all permissions on table C0012433944 to everyone except sysadmins
use [CreditDataWarehouse]
go
DENY delete ON OBJECT::dbo.C0012433944 TO cdw_Asm_User;
GO
DENY insert ON OBJECT::dbo.C0012433944 TO cdw_Asm_User;
GO
DENY references ON OBJECT::dbo.C0012433944 TO cdw_Asm_User;
GO
DENY select ON OBJECT::dbo.C0012433944 TO cdw_Asm_User;
GO
DENY update ON OBJECT::dbo.C0012433944 TO cdw_Asm_User;
GO
DENY delete ON OBJECT::dbo.C0012433944 TO CDW_Dev;
GO
DENY insert ON OBJECT::dbo.C0012433944 TO CDW_Dev;
GO
DENY references ON OBJECT::dbo.C0012433944 TO CDW_Dev;
GO
DENY select ON OBJECT::dbo.C0012433944 TO CDW_Dev;
GO
DENY update ON OBJECT::dbo.C0012433944 TO CDW_Dev;
GO
DENY delete ON OBJECT::dbo.C0012433944 TO CDW_Web;
GO
DENY insert ON OBJECT::dbo.C0012433944 TO CDW_Web;
GO
DENY references ON OBJECT::dbo.C0012433944 TO CDW_Web;
GO
DENY select ON OBJECT::dbo.C0012433944 TO CDW_Web;
GO
DENY update ON OBJECT::dbo.C0012433944 TO CDW_Web;
GO
DENY delete ON OBJECT::dbo.C0012433944 TO CMPROD;
GO
DENY insert ON OBJECT::dbo.C0012433944 TO CMPROD;
GO
DENY references ON OBJECT::dbo.C0012433944 TO CMPROD;
GO
DENY select ON OBJECT::dbo.C0012433944 TO CMPROD;
GO
DENY update ON OBJECT::dbo.C0012433944 TO CMPROD;
GO
DENY delete ON OBJECT::dbo.C0012433944 TO [DBG\cdw_dataloader-g];
GO
DENY insert ON OBJECT::dbo.C0012433944 TO [DBG\cdw_dataloader-g];
GO
DENY references ON OBJECT::dbo.C0012433944 TO [DBG\cdw_dataloader-g];
GO
DENY select ON OBJECT::dbo.C0012433944 TO [DBG\cdw_dataloader-g];
GO
DENY update ON OBJECT::dbo.C0012433944 TO [DBG\cdw_dataloader-g];
GO
DENY delete ON OBJECT::dbo.C0012433944 TO [DBG\philma];
GO
DENY insert ON OBJECT::dbo.C0012433944 TO [DBG\philma];
GO
DENY references ON OBJECT::dbo.C0012433944 TO [DBG\philma];
GO
DENY select ON OBJECT::dbo.C0012433944 TO [DBG\philma];
GO
DENY update ON OBJECT::dbo.C0012433944 TO [DBG\philma];
GO
DENY delete ON OBJECT::dbo.C0012433944 TO [dbg\svc_CDW_SSAS];
GO
DENY insert ON OBJECT::dbo.C0012433944 TO [dbg\svc_CDW_SSAS];
GO
DENY references ON OBJECT::dbo.C0012433944 TO [dbg\svc_CDW_SSAS];
GO
DENY select ON OBJECT::dbo.C0012433944 TO [dbg\svc_CDW_SSAS];
GO
DENY update ON OBJECT::dbo.C0012433944 TO [dbg\svc_CDW_SSAS];
GO
— Create Switcher job on both SQL Instances
USE [msdb]
GO
/ Object: Job [CreditDataWarehouse DB Encryption switcher] Script Date: 06/05/2019 10:11:32 / BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 / Object: JobCategory [[Uncategorized (Local)]] Script Date: 06/05/2019 10:11:32 /
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’CreditDataWarehouse DB Encryption switcher’,
@enabled=0,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’A job to monitor the CreditDataWarehouse database for failover and if one is spotted to update the master key to re-enable decryption.’,
@category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’sa’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/ Object: Step [Check for failover and if so refresh master key] Script Date: 06/05/2019 10:11:32 /
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Check for failover and if so refresh master key’,
@step_id=1,
@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’IF EXISTS(select * from sys.databases where name=”CreditDataWarehouse” and state_desc=”ONLINE”)
BEGIN
IF EXISTS(SELECT * FROM CreditDataWarehouse.dbo.C0012433944 WHERE [servername] != @@SERVERNAME)
BEGIN
DECLARE @PASSWORD VARCHAR(128)
SELECT @PASSWORD = secret FROM CreditDataWarehouse.dbo.C0012433944
DECLARE @SQL VARCHAR(1000) = ”use CreditDataWarehouse; OPEN MASTER KEY DECRYPTION BY PASSWORD = ””” + @PASSWORD + ”””; ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY ; ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;CLOSE MASTER KEY; ”
DECLARE @SQL2 VARCHAR(1000) = ”update CreditDataWarehouse.dbo.C0012433944 set [servername] = @@servername;”
EXEC(@SQL)
EXEC(@SQL2)
PRINT ”OPEN MASTER KEY DECRYPTION”
END
END
GO’,
@database_name=N’master’,
@flags=0
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’Every 10 seconds’,
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=2,
@freq_subday_interval=10,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20190430,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N’c384ab01-0fa6-400c-a797-e80fbabf7c20′
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