change db owner / set read_only with clone template .. ?
I realize I could do the subject tasks in a separate step using a number of different approaches -- but I'm curious why I can't during the cloning process with a template. The help files tell me that the template is run after the clone is created so the 'database' exists with the name it needs to be. As part of our compliance and standards, I set all new database owners to a non-transitory AD account; when the clone is created, the owner is the account which I'm using to run the image=>clone process. When I execute:
alter authorization on database::<db name> to [domain\user];
.. the logs tell me I can't do it because either the account doesn't exist or I don't have permissions. I know the account exists (the other db's on the instance have this owner) and I know I have permissions (the context under which the cloning process is running is provisioned into [sysadmin] -- note this is different than I want the db owner to be). So - is the process not running under the [sysadmin] account, after all?
Further, I have an additional requirement that sets this particular clone to read_only. When the code executes, the error is that the clone cannot be modified because it is set to read_only. So it would seem that the template is not run as the last thing the cloning process does -- is that a correct observation?
Thank you!
Tagged:
Best Answer
-
Panagis
Posts: 9 Bronze 3
Hello @don_don,
SQL Clone creates a temporary low privilege user account to run the template script. The only role of this account is the db_owner.
The first issue arises because this account hasn't permissions to alter the authorization of the database, and the second because at the last step it tries to delete the temporary user, but it can't since the database is read-only.
What I'd suggest is to create a PowerShell script that will call the Clone cmdlet and after that it will alter the authorization of the database and make it read-only.
Kind regards,
Panagis
Answers