Here is the updated script with parameters:
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_EncryptDBs')
DROP PROCEDURE dbo.sp_EncryptDBs
GO
CREATE PROCEDURE [dbo].[sp_EncryptDBs]
@CertName NVARCHAR(128) = NULL,
@BackupLocation NVARCHAR(256) = NULL,
@Password NVARCHAR(128) = NULL,
@ExpirationDate DATE = NULL,
@MasterKeyPassword NVARCHAR(128) = NULL,
@ExcludedDatabases NVARCHAR(MAX) = NULL,
@DropMasterKey NVARCHAR(3) = 'NO'
AS
BEGIN
SET ANSI_WARNINGS OFF;
SET XACT_ABORT, NOCOUNT ON;
-- Check if the backup location is valid and accessible
DECLARE @BackupLocationExists INT = 1
IF @BackupLocation IS NOT NULL
BEGIN
BEGIN TRY
EXEC master.dbo.xp_create_subdir @BackupLocation
END TRY
BEGIN CATCH
SET @BackupLocationExists = 0
END CATCH
PRINT 'Checking backup location: ' + @BackupLocation;
PRINT 'Backup location exists: ' + CASE WHEN @BackupLocationExists = 1 THEN 'Yes' ELSE 'No' END;
IF @BackupLocationExists = 0
BEGIN
PRINT 'Backup location is not valid or accessible. Cannot proceed with the operation.';
RETURN;
END
END
-- Create a new certificate if parameters are specified
IF @CertName IS NOT NULL
BEGIN
-- If @MasterKeyPassword is not provided, cannot proceed with creating a new certificate
IF @MasterKeyPassword IS NULL
BEGIN
PRINT 'Cannot create a new certificate because the master key password is not provided.';
RETURN;
END
-- Try to open the master key
BEGIN TRY
DECLARE @OpenMasterKeyCommand NVARCHAR(MAX) = N'
OPEN MASTER KEY DECRYPTION BY PASSWORD = ''' + @MasterKeyPassword + '''';
EXEC sp_executesql @OpenMasterKeyCommand;
PRINT 'Opened master key';
END TRY
BEGIN CATCH
PRINT 'Cannot open master key with the provided password.';
RETURN;
END CATCH
-- If @DropMasterKey is 'YES', drop the encryption keys, certificate, and master key
IF @DropMasterKey = 'YES'
BEGIN
-- Find databases with an encryption key and drop it
DECLARE @DatabaseWithKey NVARCHAR(128)
DECLARE cur CURSOR FOR
SELECT db_name(database_id) FROM sys.dm_database_encryption_keys
OPEN cur
FETCH NEXT FROM cur INTO @DatabaseWithKey
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @DropKeyCommand NVARCHAR(MAX) = N'
USE [' + @DatabaseWithKey + ']
DROP DATABASE ENCRYPTION KEY';
EXEC sp_executesql @DropKeyCommand;
PRINT 'Dropped encryption key for database: ' + @DatabaseWithKey;
FETCH NEXT FROM cur INTO @DatabaseWithKey
END
CLOSE cur
DEALLOCATE cur
-- Find the certificate used for TDE and drop it
DECLARE @CertNameToDrop NVARCHAR(128) =
(SELECT TOP 1 c.name FROM sys.certificates c WHERE c.pvt_key_encryption_type_desc = 'DATABASE')
IF @CertNameToDrop IS NOT NULL
BEGIN
DECLARE @DropCertCommand NVARCHAR(MAX) = N'
USE master
DROP CERTIFICATE [' + @CertNameToDrop + ']';
EXEC sp_executesql @DropCertCommand;
PRINT 'Dropped certificate: ' + @CertNameToDrop;
END
-- Drop the master key
EXEC sp_executesql N'DROP MASTER KEY';
PRINT 'Dropped master key';
END
-- Switch to master database
EXEC sp_executesql N'USE master';
-- Create a new certificate
DECLARE @CreateCertCommand NVARCHAR(MAX) = N'
CREATE CERTIFICATE [' + @CertName + ']
WITH SUBJECT = ''Database Encryption'',
EXPIRY_DATE = ''' + CONVERT(NVARCHAR(10), @ExpirationDate, 120) + '''';
EXEC sp_executesql @CreateCertCommand;
-- Backup the certificate
IF @BackupLocation IS NOT NULL AND @Password IS NOT NULL
BEGIN
DECLARE @BackupCertCommand NVARCHAR(MAX) = N'
BACKUP CERTIFICATE [' + @CertName + ']
TO FILE = ''' + @BackupLocation + '\' + @CertName + '.cer''
WITH PRIVATE KEY (FILE = ''' + @BackupLocation + '\' + @CertName + '.pvk'',
ENCRYPTION BY PASSWORD = ''' + @Password + ''')';
EXEC sp_executesql @BackupCertCommand;
PRINT 'Created and backed up new certificate: ' + @CertName;
END
END
ELSE
BEGIN
-- Check if there is an existing, non-expired certificate that is used for encryption in a user database
SET @CertName = (
SELECT TOP 1 c.name
FROM sys.certificates c
INNER JOIN sys.dm_database_encryption_keys dek ON dek.encryptor_thumbprint = c.thumbprint
WHERE c.expiry_date > GETDATE()
)
IF @CertName IS NULL
BEGIN
PRINT 'Cannot find an existing, non-expired certificate. Please provide a certificate name.';
RETURN;
END
PRINT 'Using existing certificate: ' + @CertName;
END
-- Split the excluded databases into a table
DECLARE @ExcludedDatabasesTable TABLE (DatabaseName NVARCHAR(128))
DECLARE @DatabaseName NVARCHAR(128)
WHILE CHARINDEX(',', @ExcludedDatabases) > 0
BEGIN
SELECT @DatabaseName = LTRIM(RTRIM(SUBSTRING(@ExcludedDatabases, 1, CHARINDEX(',', @ExcludedDatabases) - 1))),
@ExcludedDatabases = LTRIM(RTRIM(SUBSTRING(@ExcludedDatabases, CHARINDEX(',', @ExcludedDatabases) + 1, LEN(@ExcludedDatabases))))
INSERT INTO @ExcludedDatabasesTable (DatabaseName) VALUES (@DatabaseName)
END
INSERT INTO @ExcludedDatabasesTable (DatabaseName) VALUES (@ExcludedDatabases)
-- Encrypt databases that are not yet encrypted and not in the excluded list
IF OBJECT_ID('tempdb..#Databases') IS NOT NULL
DROP TABLE #Databases
SELECT
db_name(D.database_id) [Database]
INTO #Databases
FROM
sys.databases D
WHERE
D.is_encrypted = 0
AND db_name(D.database_id) NOT IN
(
'Master',
'model',
'msdb'
)
AND db_name(D.database_id) NOT IN
(
SELECT DatabaseName FROM @ExcludedDatabasesTable
)
AND D.state_desc = 'Online'
DECLARE @Count INT = (SELECT COUNT(*) FROM #Databases);
PRINT 'Number of databases to encrypt: ' + CAST(@Count AS NVARCHAR(10));
DECLARE @Database NVARCHAR(50);
DECLARE db_cursor CURSOR FOR
SELECT [Database] FROM #Databases;
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Current Database: ' + @Database;
DECLARE @Command1 NVARCHAR(MAX) = N'
USE [' + @Database + ']
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE [' + @CertName + ']'
DECLARE @Command2 NVARCHAR(MAX) = N'
ALTER DATABASE [' + @Database + ']
SET ENCRYPTION ON
'
PRINT 'Encrypting Database: ' + @Database;
PRINT 'Executing command: ' + @Command1;
BEGIN TRY
EXEC sp_executesql @Command1;
END TRY
BEGIN CATCH
PRINT 'Error executing command: ' + ERROR_MESSAGE();
END CATCH
PRINT 'Executing command: ' + @Command2;
BEGIN TRY
EXEC sp_executesql @Command2;
END TRY
BEGIN CATCH
PRINT 'Error executing command: ' + ERROR_MESSAGE();
END CATCH
FETCH NEXT FROM db_cursor INTO @Database
END
CLOSE db_cursor
DEALLOCATE db_cursor
SET @Count = (SELECT COUNT(*) FROM #Databases);
PRINT 'Number of databases after loop: ' + CAST(@Count AS NVARCHAR(10));
DROP TABLE #Databases;
-- Switch encryption to the new or existing certificate for databases that have an expired certificate
SELECT db_name(database_id) [Database]
INTO #Databases2
FROM
sys.dm_database_encryption_keys K
INNER JOIN sys.certificates C ON K.encryptor_thumbprint = C.thumbprint
WHERE
C.expiry_date < GETDATE()
WHILE (SELECT COUNT(*) FROM #Databases2) > 0
BEGIN
DECLARE @Database2 NVARCHAR(50) = (SELECT TOP 1 [Database] FROM #Databases2)
DECLARE @Command3 NVARCHAR(MAX) = N'
USE [' + @Database2 + ']
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE [' + @CertName + ']'
PRINT 'Switching encryption to certificate ' + @CertName + ' for Database: ' + @Database2;
EXEC sp_executesql @Command3;
DELETE FROM #Databases2 WHERE [Database] = @Database2
END
DROP TABLE #Databases2
-- Close the master key, if it was opened
IF @CertName IS NOT NULL AND @CertName = @CertName
BEGIN
EXEC sp_executesql N'CLOSE MASTER KEY';
PRINT 'Closed master key';
END
PRINT 'Database encryption completed successfully.';
END
GO