Exception when trying to run SQL script during the creation of an Image
Hello,
I am trying to automate the creation of Images and clones , for this purpose I need to run a SQL script during the creation of the image , my SQL script will use xp_CMDShell to run another Powershell Script like :
master..xp_CMDShell 'Powershell.exe -Command D\Test.ps1'
so when I execute my automation script , it fails with the following error message :
The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.
as I found out, the user must have sysadmin privilege to be able to run xp_cmdShell , but I don't know which user is being used to create the image,
so could you please help me to resolve this error ?
Thanks
I am trying to automate the creation of Images and clones , for this purpose I need to run a SQL script during the creation of the image , my SQL script will use xp_CMDShell to run another Powershell Script like :
master..xp_CMDShell 'Powershell.exe -Command D\Test.ps1'
so when I execute my automation script , it fails with the following error message :
The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.
as I found out, the user must have sysadmin privilege to be able to run xp_cmdShell , but I don't know which user is being used to create the image,
so could you please help me to resolve this error ?
Thanks
Tagged:
Answers
The Clone agent creates a temporary user on the copied image database, gives it high privileges just on that database (db_owner), and then impersonates it to run your scripts. We implemented it that way for the first pass to keep the scripts from running under Clone agent's (normally high) server-level privileges.
As such, the temporary user is prevented from doing work outside that specific database (i.e. performing server-level activities and reading from or writing to other databases), which will include the use of xp_CMDShell.
I'd love to hear about what you were trying to get done with PowerShell, and how you were blocked with the current implementation. The current process is very much a work in progress - we're trying to get it to a place where it's easy to do whatever you need to do, as safely as possible. User feedback helps us figure out what we need to build next.
Redgate Software
The thing that we want to do is running an executable that will set some application security on the database during the creation of the Image , so for now I can exclude it from my script and put it in a job to run , but it will be a nice to have feature to be able to use xp_CMDShell in this step,
Thanks,
We'll factor your feedback into our next-step discussions for this feature.
Redgate Software