Brandie Tarvin (11/5/2015)
Found it! Found it!
create credential ##xp_cmdshell_proxy_account## with identity = 'Domain\DomainUser', secret = 'password'
This let me create the credential for the service account without having to create a proxy account (which would require a lot of stuff having to be recoded).
Yes, I know. Granting the service account access to xp_cmdshell is definitely not the best solution. In the future I will push for using a different method of running this particular web page. But right now, it works. This webpage is an internal dashboard of jobs and processes that we monitor. No one outside IT has access to the page or the code behind the page.
YES. Problem solved. Thank you everyone for pointing me down the right path.
First of all, I'm glad you have it working. However, there are still a couple of things to consider. First of all, don't forget to revoke that CONTROL SERVER permission you granted earlier. 😉
I thought you were connecting as yourself (a user with sysadmin privs) to create the procedure. In that case, you should have been able to create the procedure WITH EXECUTE AS OWNER and then granted EXECUTE on the procedure to the login used by the web site. If you were connecting as the db_owner instead of yourself, then that explains why it didn't work. If you were connecting as yourself and have sysadmin, then I'm at a complete loss as to what went wrong.
Personally, I don't like the idea of having EXECUTE on xp_cmdshell specifically granted to a login - any login, but especially a SQL login instead of a Windows login. I think it should be reserved for DBAs. I consider it dangerous because you can't predict now how that login is going to be used in the future and you don't want a bunch of different applications having the ability to run xp_cmdshell. I'm certainly not against xp_cmdshell...far from it, in fact. However, I do think that it should be controlled.
Now that you have it working and know how you did it, are you open to trying it the other way? I may be being overly cautious for your situation, but I don't want to see it come back and bite you down the road.