There are times when I need to run some process using credentials other than my current security context, such as when my userid doesn't have permissions to a remote resource but another userid does. I can easily do this interactively via the "Run as..." option (right click a process and then enter credentials) or via the runas command in a cmd window. The problem with these though is that I have to type in a password, which creates a problem when I want to do this as a scheduled job. In the past, I've written a batch file to map a drive using the other credentials via the NET USE command. I've never liked that approach so when I recently needed to revisit this, I decided I'd look for alternative solutions.
I tried three different solutions: adding an echo to the runas command, runasspc, and CPAU.
The first solution didn't work at all for me. I found several pages that suggested piping the password with the runas command via echo command. I didn't see any evidence that this approach worked, but rather they were suggesting to try it. Here's what I tried:
echo password | runas /user:DomainName\UserName E:\Folder1\Process1.exe
I couldn't get the second solution, runasspc, to work, but I'm sure I just didn't try hard enough. I had very little patience that day. You don't need to install the tool, but you will need to copy all of the files and subdirectories if you want it to run on other servers. Here is an example call:
runasspc.exe /program:"E:\Folder1\Process1.exe" /domain:"DomainName /user:"UserName" /password:"password"
The third solution, CPAU, worked great and didn't have any other dependent files which makes it easy to setup other servers with it. Here are some example calls:
E:\CPAU\CPAU.exe -u DomainName\UserName -p password -ex E:\Folder1\Process1.exe -nowarn
E:\CPAU\CPAU.exe -u DomainName\UserName -p password -ex E:\Folder1\BatchFile1.cmd -nowarn
E:\CPAU\CPAU.exe -u DomainName\UserName -p password -ex "xcopy \\Server1\Share1\File1.txt E:\Folder1\ /Y" -nowarn
Any of the above example calls would solve my current issue, however I ended up using the last one since I'm simply doing a file copy. The third one is better than the second one as I don't need an extra file to do the xcopy.
Here's what I embedded into my stored procedure that copies the production databases down to development:
EXEC master.sys.xp_cmdshell 'E:\CPAU\CPAU.exe -u DomainName\UserName -p password -ex "xcopy \\Server1\Share1\File1.txt E:\Folder1\ /Y" -nowarn -wait -hide', NO_OUTPUT
There are other ways to do this, but CPAU works great for me and is lightweight. Let me know if there is something better out there though.