I've created a procedure as a work around for our testers to be able to start and stop a perfmon trace when they run load tests.
Yes, I know, Powershell would have been a better choice. But I am a PS novice, and it was far easier to give them a proc to run as opposed to granting permissions, RDP access, and so forth.
I created a domain account, and added it to the proper groups on the server.
I added this account to SQL, made it sysadmin, and disabled the login.
I created the proc with the EXECUTE AS this user.
The problem is that the proxy credentials seem to stop working. You can run this proc successfully, but if you attempt to run it again, it fails with this error:
Procedure: xp_cmdshell Message: An error occurred during the execution of xp_cmdshell. A call to 'LogonUserW' failed with error code: '1385'. Line Number: 1
I then drop and re-add the proxy account with this code
EXEC sp_xp_cmdshell_proxy_account NULL;
EXEC sp_xp_cmdshell_proxy_account 'MyDomain\TheUser', 'TheUsersPassword';
It then will run successfully.
There is no pattern to when it fails.