Modify database ownership and settings
Published 14 December 2020
Notable points to remember about pre-deployment and post-deployment scripts
- Pre and post deployment scripts must be re-runnable as they will be executed as part of every deployment.
- Pre and post deployment scripts are deployed to every environment
- You need to provide your own error handling as desired in pre and post deployment scripts - the examples below use a TRY/CATCH pattern
- See the main page regarding ordering, transaction management, and more
Considerations when using data virtualization
If you are using a SQL Clone image as a baseline, most database properties which are set on the production instance will carry through with it in the cloning process.
The database owner is an exception to this rule.
Pre-Deployment script to set the database owner
In this example, we check the database owner, then set it to the built-in sa account if it is set to another value.
In SQL Server database ownership defaults to the account which ran the script to create the database, but in practice typically it makes sense to set the built in owner account as sa.
Depending on how you have database ownership configured in production, you might use a guard clause to set this only for certain deployment targets by checking DEFAULT_DOMAIN() or @@SERVERNAME.
Pre-deployment script to set the database owner
/*
Pre-Deployment Script Example
--------------------------------------------------------------------------------------
This file contains SQL statements that will be prepended to the build script.
This example requires SQLCMD syntax
For more information, see https://documentation.red-gate.com/display/sca4/Pre-deployment+and+post-deployment+scripts
--------------------------------------------------------------------------------------
*/
/* This may be be uncommented and edited to use a SQLCMD variable for testing in SSMS
under SQLCMD mode (Query menu -> SQLCMD), but remove or comment this out before committing. */
--:setvar DatabaseName YourDatabaseName
SET XACT_ABORT, NOCOUNT ON;
GO
USE [$(DatabaseName)];
GO
BEGIN TRY
DECLARE @msg NVARCHAR(1000) = N'';
IF
(
SELECT COUNT(*)
FROM sys.databases
WHERE name = DB_NAME()
AND SUSER_SNAME(owner_sid) = 'sa'
) = 0
BEGIN
RAISERROR(N'Changing owner of $(DatabaseName) to sa', 0, 0) WITH NOWAIT;
EXEC ('ALTER AUTHORIZATION ON DATABASE::[$(DatabaseName)] TO [sa];')
END;
END TRY
BEGIN CATCH
IF @@trancount > 0
ROLLBACK TRANSACTION;
SET @msg = ERROR_MESSAGE();
RAISERROR(@msg, 16, 1);
END CATCH;
GOPre-Deployment script to set the database recovery model
In this example, we check the target database's recovery model and change it to SIMPLE if it does not already have that setting.
The appropriate recovery model for your database may be something different. If you aren't sure what is right for your environment, start with this overview of recovery models.
Pre-deployment script to set the database recovery model
/*
Pre-Deployment Script Example
--------------------------------------------------------------------------------------
This file contains SQL statements that will be prepended to the build script.
This example requires SQLCMD syntax
For more information, see https://documentation.red-gate.com/display/sca4/Pre-deployment+and+post-deployment+scripts
--------------------------------------------------------------------------------------
*/
/* This may be be uncommented and edited to use a SQLCMD variable for testing in SSMS
under SQLCMD mode (Query menu -> SQLCMD), but remove or comment this out before committing. */
--:setvar DatabaseName YourDatabaseName
USE [$(DatabaseName)];
GO
SET XACT_ABORT, NOCOUNT ON;
GO
BEGIN TRY
DECLARE @msg NVARCHAR(1000) = N'';
IF
(
SELECT COUNT(*)
FROM sys.databases
WHERE name = DB_NAME()
AND recovery_model_desc = 'SIMPLE'
) = 0
BEGIN
RAISERROR(N'Changing recovery model of $(DatabaseName) to SIMPLE', 0, 0) WITH NOWAIT;
ALTER DATABASE CURRENT SET RECOVERY SIMPLE;
END;
END TRY
BEGIN CATCH
IF @@trancount > 0
ROLLBACK TRANSACTION;
SET @msg = ERROR_MESSAGE();
RAISERROR(@msg, 16, 1);
END CATCH;
GO
