September 18, 2007 at 3:39 pm
Hi all,
I have a problem while i create a proxy account.The situation is like this...There is a user who has an login in to the server.He has a stored procedure which calls some on the SSIS packages and XP_cmdshell...so this stored procedure basically load some data in to the tables .So for the login in order to execute the stored procedure as he is not a Sys admin I have created a proxy account in my account as Iam an SA and then in the proxies and in principals I selected his login name and this way I have created a credential and a proxy account.
Now the problem is if he logins with his id and password and try to execute the stored procedure it gives an error message
Server: Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
....so this mean the login is not able to see the proxy account.So what I did is I created a job and then in the job owner tab I have selected his login and then created a step with the type operating system (CmdExec) as I need to just execute the stored procedure and used the proxy account that I have created.
so I gave the command -- exec <stored procedure> --.
But this job fails and gives the error message as
[298] SQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. [SQLSTATE 42000]....
So now ....first My question is am I doing in a right way....if its right then why Iam not able to execute the stored procedure.
If there is any other way through which I can execute the stored procedure using a proxy account for the logins who are not sys admins....please do let me know.....
Thanks
Raja.V
September 19, 2007 at 7:47 am
In SQL 2005, the proxy account for xp_cmdshell and proxy for CmdExec job step is different.
If you want to run the SP, set the proxy accoutn as:
EXEC
sp_xp_cmdshell_proxy_account 'Domain\User','Pass'
and you still need to ad the user to master database, and grant exec privilege on the xp_cmdshell to the user
March 28, 2008 at 11:47 am
I've tried this and users still get the following error.
Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
the xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
Any idea what else it could be?
¤ §unshine ¤
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply