SQL Clone Instance Creation

sdudzic

I am cloning a database which has CLR functionality and I want to run the following commands when creating the image:

sp_configure 'show advanced options', 1;  

GO  

RECONFIGURE;  

GO  

sp_configure 'clr enabled', 1;  

GO  

RECONFIGURE;  

GO  

This does not work when applied as a SQL modification script when creating the image.  The error is:
"An exception was thrown while executing a script: System.Data.SqlClient.SqlError: User does not have permission to perform this action."

I have confirmed that the user accessing the database has sysadmin permissions and other data manipulation activities work fine (it just fails when trying to do the reconfigure statement).

Other notes:  I am creating the image from a database backup.  Creating through the UI will fail and then I have access to the error message.  Howerver, when running on Powershell, the process completes and the failure in the modification script does so silently and still creates the image. 

How can I run statements such as the above that require Reconfigure?

Tianjiao_Li

@sdudzic

The script fails as it will be run with limited privileges - as a temporary user which only has permission to modify the image as db_owner, not to make changes to the server or to other databases.
https://documentation.red-gate.com/clone/modifications-during-provisioning/image-modifications

Thanks for the feedback for lacking notifying users when running it on Powershell. I'll be sure to pass this on to the product team.

sdudzic

Thank you for the information on why it fails on the instance creation.  Do you have a recommendation on how to apply this when creating clones?  I will be regularly updating images that is cloned to 15 workstations and do not want to have each end user have to manually run the script on their workstation when I update the image.