Non-elevated user for SQL Server

Datical's Deployment Packager for SQL Server requires elevated permissions as documented here: SQL Server Roles and Permissions for Datical DB. These roles inclue:

• dbcreator - server-level permission
• db_backupoperator - database-level permission

With the new approach, those permissions are no longer needed. Instead, following database-level permissions are needed:

• db_datareader
• db_datawriter
• db_ddladmin
• db_securityadmin
• VIEW ANY DATABASE

This document discusses another approach that does not requires above-mentioned SQL Server privileges for packaging. 

Overview

This approach leverages Datical's baseline to capture the state of the database (establish baseline before you do anything else - this will populate changelog.xml with initial change sets). Then use custom backup/restore script that doesn't use the native SQL Server backup/restore mechanism (and require the associated privileges).

This script, as part of restoring the database, performs drop of all objects to clean out the database. By configuring to use "on_demand" backup mode, Deployment Packager will use the changelog instead of the backup file to restore the database.

Requirements

This approach requires the following setup:

• The database must be baselined so that all objects in the database have a corresponding change set in Datical's changelog. The baseline (change sets) serve as backup of the database from which databases will be rebuilt in case restore operation needs to be performed.
• Deployment Packager must be configured with databaseBackupMode=on_demand
• Use the attached groovy script for custom backup/restore method. 
  • Groovy script: BackupRestoreSqlServerNoBackupFile-v5.4.groovy. 
    • This script would need to be added to "extensions" directory, which requires additional property to be set in Deployment Packager configuration: extensionsPath=C://apps//DaticalDB//repl//scripts//extensions
  • As such, Deployment Packager must be configured with databaseBackupRestoreMethod=BackupRestoreSqlServerNoBackupFile

Step-by-Step

DBA:

1. Create a REF DB. This could be a blank database or generated from PROD (with no data). F
2. Setup permissions on REF database as follows:
   1. Non-elevated permisssions

      USE [EPOD_CMS_REF]
      GO
      EXEC sp_addrolemember N'db_ddladmin', N'datical_user'
      GO
      EXEC sp_addrolemember N'db_datareader', N'datical_user'
      GO
      EXEC sp_addrolemember N'db_datawriter', N'datical_user'
      GO
      EXEC sp_addrolemember N'db_securityadmin', N'datical_user'
      GO
      GRANT VIEW DATABASE STATE to [datical_user]
      GO

Datical SME:

1. Save the groovy script (BackupRestoreSqlServerNoBackupFile-v5.4.groovy) to a path within Datical install directory, e.g., C://apps//DaticalDB//repl//scripts//extensions.
   1. Rename the file to BackupRestoreSqlServerNoBackupFile.groovy
   2. The script performs the following operations:
      1. Backup: No backup is taken. This is configured in Step 3 where Datical project is configured for "on_demand" backup mode.
      2. Restore: Performs a drop of all objects by executing SQL code (built into the groovy script) and then restores/rebuilds the database from change sets in the Datical project. 
2. Provide a mechanism to build REF DB using one of the following two approaches:
   1. If the REF DB is already populated, then create a baseline of your REF database using the command: hammer groovy project_baseline.groovy
   2. If the REF DB is a blank database, then DBAs will need to provide a DDL script of all objects from production instance. This script will need to be added to sql_direct/ref_only folder containing custom metadata.properties file that mentions contexts=REF_ONLY
3. Update deployPackager.properties with these new lines:
   1. databaseBackupMode=on_demand
   2. databaseBackupRestoreMethod=BackupRestoreSqlServerNoBackupFile
   3. extensionsPath=C://apps//DaticalDB//repl//scripts//extensions
4. Ensure that REF DB has context set to REF_ONLY. This will allow load script from sql_direct/REF_ONLY 

You are now configured with non-elevated use that can run Deployment Packager. Start packager your SQL scripts.

Expected Behavior

For packaging scripts using CONVERT packaging method (ddl): Deployment Packager will always perform a restore operation. This will manifest as following intermediate deploy operation during packaging job:

For packaging scripts using STOREDLOGIC, DDL_DIRECT, DATA_DML, SQLFILE, or DIRECT packaging methods: Deployment Packager may not always perform a restore operation. The restore operation will only be invoked when a failure is encountered during the packaging job.