Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Raj.V
Starting Member
4 Posts |
 Posted - 2007-09-18 : 16:54:02
|
| 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 1The 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 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-18 : 22:42:15
|
| Did you set sql agent proxy account? |
 |
|
|
Raj.V
Starting Member
4 Posts |
Posted - 2007-09-19 : 02:07:16
|
| Yes...I even have set the SQL agent proxy even then Iam getting the same problem |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-19 : 23:08:21
|
| What kind of permission does proxy account have? |
|
|
|
Raj.V
Starting Member
4 Posts |
Posted - 2007-09-20 : 13:21:58
|
| Iam not sure abt what kind of permissions you are talking abt..Do you mean the permissions to execute the stored procedure???.....so in that case I gave the execute permission to that particular stored procedure and apart from that also gave the execute permissions to the xp_cmdshell.And while careting the proxy account in the principles column I have added the user name in there.Is there anything I need to give..... If there is any can you please let me know what to do....Thanks |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-20 : 23:16:29
|
| Proxy account needs more permission than exec on xp_cmdshell in sql server, otherwise you can just grant exec on xp_cmdshell to user. |
|
|
|
|
|
|
Execute a stored procedure using a proxy account