Non-elevated user for SQL Server

Datical's Deployment Packager for SQL Server requires elevated permissions as documented here: /wiki/spaces/DDOC59/pages/795738600. 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 Packger 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. 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. Create baseline of your REF database using the command: hammer groovy project_baseline.groovy
2. 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
3. Update deployPackager.properties with these new lines:
   1. databaseBackupMode=on_demand
   2. databaseBackupRestoreMethod=BackupRestoreSqlServerNoBackupFile
   3. extensionsPath=C://apps//DaticalDB//repl//scripts//extensions

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. 

For packaging scripts using STOREDLOGIC, DATA_DML 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.