SQL Clone image modifications

RichardMGreen

Hi all

We've got the hang on creating images and clones using PowerShell but now run into a new snag.

We've got a couple of modification scripts we want to run when the image is created but I'm getting error messages.

The first script

• alters the clone owner to "sa" (that account is disabled but it keeps all the databases consistent)
• drop a few stored procedures and functions that use some SQLCLR functions we've created
• installs some new assemblies
• rebuilds the stored procedures and functions to use the newly installed assemblies

This is the one that's failing.
This is the entire error message:-

An exception was thrown while executing a script: Microsoft.Data.SqlClient.SqlError: Cannot find the principal 'sa', because it does not exist or you do not have permission. Microsoft.Data.SqlClient.SqlError: User does not have permission to perform this action. Microsoft.Data.SqlClient.SqlError: ALTER DATABASE statement failed. Microsoft.Data.SqlClient.SqlError: User does not have permission to perform this action. Microsoft.Data.SqlClient.SqlError: You do not have permission to run the RECONFIGURE statement. Microsoft.Data.SqlClient.SqlError: UNSAFE ASSEMBLY permission was denied on object 'server', database 'master'. Microsoft.Data.SqlClient.SqlError: UNSAFE ASSEMBLY permission was denied on object 'server', database 'master'. Microsoft.Data.SqlClient.SqlError: UNSAFE ASSEMBLY permission was denied on object 'server', database 'master'. Microsoft.Data.SqlClient.SqlError: UNSAFE ASSEMBLY permission was denied on object 'server', database 'master'. Microsoft.Data.SqlClient.SqlError: UNSAFE ASSEMBLY permission was denied on object 'server', database 'master'. Microsoft.Data.SqlClient.SqlError: UNSAFE ASSEMBLY permission was denied on object 'server', database 'master'. Microsoft.Data.SqlClient.SqlError: Assembly 'CLR_Project' was not found in the SQL catalog of database 'SqlCloneTemp_woaa0lma'. Microsoft.Data.SqlClient.SqlError: Assembly 'CLR_Project' was not found in the SQL catalog of database 'SqlCloneTemp_woaa0lma'. Microsoft.Data.SqlClient.SqlError: Assembly 'CLR_Project' was not found in the SQL catalog of database 'SqlCloneTemp_woaa0lma'. Microsoft.Data.SqlClient.SqlError: Assembly 'CLR_Project' was not found in the SQL catalog of database 'SqlCloneTemp_woaa0lma'. Microsoft.Data.SqlClient.SqlError: Assembly 'CLR_Project' was not found in the SQL catalog of database 'SqlCloneTemp_woaa0lma'. Microsoft.Data.SqlClient.SqlError: Assembly 'CLR_Project' was not found in the SQL catalog of database 'SqlCloneTemp_woaa0lma'. Microsoft.Data.SqlClient.SqlError: Assembly 'CLR_Project' was not found in the SQL catalog of database 'SqlCloneTemp_woaa0lma'. Microsoft.Data.SqlClient.SqlError: Assembly 'CLR_Project' was not found in the SQL catalog of database 'SqlCloneTemp_woaa0lma'. Microsoft.Data.SqlClient.SqlError: Assembly 'CLR_Project' was not found in the SQL catalog of database 'SqlCloneTemp_woaa0lma'. Microsoft.Data.SqlClient.SqlError: Assembly 'CLR_Project' was not found in the SQL catalog of database 'SqlCloneTemp_woaa0lma'. Microsoft.Data.SqlClient.SqlError: Assembly 'CLR_Project' was not found in the SQL catalog of database 'SqlCloneTemp_woaa0lma'. Microsoft.Data.SqlClient.SqlError: Assembly 'CLR_Project' was not found in the SQL catalog of database 'SqlCloneTemp_woaa0lma'. Microsoft.Data.SqlClient.SqlError: Assembly 'CLR_Project' was not found in the SQL catalog of database 'SqlCloneTemp_woaa0lma'. Microsoft.Data.SqlClient.SqlError: Assembly 'CLR_Project' was not found in the SQL catalog of database 'SqlCloneTemp_woaa0lma'. Microsoft.Data.SqlClient.SqlError: Assembly 'CLR_Project' was not found in the SQL catalog of database 'SqlCloneTemp_woaa0lma'. Microsoft.Data.SqlClient.SqlError: Assembly 'CLR_Project' was not found in the SQL catalog of database 'SqlCloneTemp_woaa0lma'. Microsoft.Data.SqlClient.SqlError: Assembly 'CLR_Project' was not found in the SQL catalog of database 'SqlCloneTemp_woaa0lma'. Microsoft.Data.SqlClient.SqlError: Assembly 'CLR_Project' was not found in the SQL catalog of database 'SqlCloneTemp_woaa0lma'. Microsoft.Data.SqlClient.SqlError: Assembly 'CLR_Project' was not found in the SQL catalog of database 'SqlCloneTemp_woaa0lma'. Microsoft.Data.SqlClient.SqlError: Assembly 'CLR_Project' was not found in the SQL catalog of database 'SqlCloneTemp_woaa0lma'. Microsoft.Data.SqlClient.SqlError: Assembly 'CLR_Project' was not found in the SQL catalog of database 'SqlCloneTemp_woaa0lma'. Microsoft.Data.SqlClient.SqlError: Assembly 'CLR_Project' was not found in the SQL catalog of database 'SqlCloneTemp_woaa0lma'. Microsoft.Data.SqlClient.SqlError: Assembly 'CLR_Project' was not found in the SQL catalog of database 'SqlCloneTemp_woaa0lma'. Microsoft.Data.SqlClient.SqlError: Assembly 'CLR_Project' was not found in the SQL catalog of database 'SqlCloneTemp_woaa0lma'. Microsoft.Data.SqlClient.SqlError: Assembly 'CLR_Project' was not found in the SQL catalog of database 'SqlCloneTemp_woaa0lma'. (ScriptRunnerException)

The second script:-

• Drop all foreign key constraints
• truncates all the tables
• repoints all synonyms to our test environment
• recreates all the foreign key constrains

Both scripts work perfectly in SSMS but I can't get the first one to run from PowerShell so I don't know if the second one will run.

Any help on this would be appreciated.

Regards

Richard

DustinM

Hi Richard,
 
Thank you for your inquiry into SQL Clone.
 
You may be running into a limitation with SQL Clone when attempting to make modifications:

"The scripts 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/t-sql-modifications

I hope this information helps.
 
Best,
 
Dustin 

TheMaskedData

@RichardMGreen Q1 - are you creating the image from a backup file or from a live DB copy and Q2 - are you also using Data Masker for any obfuscation of the Image data?

RichardMGreen

@DustinM - Thanks for that.  Is there any way around this?

@TheMaskedData - It's from a live db and we are not using Data Masker.  If we can get this working, we will be truncating all the tables and repointing synonyms to our test database(s)

TheMaskedData

@RichardMGreen I think the easiest way around it (without using Data Masker) is to copy or restore a backup of the DB first using DBATools or other similar method via PowerShell, then run the modification scripts, then create the Image from that copy of the DB.

This could at least have the unintended benefit of ensuring all transactions were completed before creating the image meaning they won't be replayed against the clones...