Non-elevated user for SQL Server

Datical's Deployment Packager for SQL Server requires elevated permissions.

• 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 abovementioned 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

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.