July 26, 2005 at 10:47 pm
Dear all,
I think this is a typical problem but I have followed the solutions from BOL yet the error is still there:
In my stored procedure, I included a call to xp_cmdshell to execute a DTS package using DTSRUN (the DTS package performs some processing in Analysis Server), an an OS command. From my application, I used a non-sysadmin SQL Server login called APP_USER to connect to SQL Server, and it maps to database user APP_USER in databases master and APP (the application database).
I granted execute permission on xp_cmdshell to the user APP_USER in master database, and then run the xp_sqlagent_proxy_account as follows:
EXEC master.dbo.xp_sqlagent_proxy_account N'SET',
N'APP-SERVER',
N'administrator',
N'password'
The 3 parameters are the SQL Server machine name, administrator user name and its password, respectively.
So I run the stored procedure using the APP_USER account in query analyzer, yet at the line the xp_cmdshell is called, the following error occurs:
Msg 50001, Level 1, State 50001
xpsql.cpp: Error 87 from GetProxyAccount on line 604
Did I do anything wrong? Or what else must I do to let APP_USER successfully run the stored procedure?
Many thanks,
delpiero
July 27, 2005 at 7:49 pm
I think I have finally found the solution by myself. What I was missing is to uncheck the box "Only users with SysAdmin privileges can execute CmdExec and Active Scripting job steps" in the "Non-SysAdmin job step proxy account" part, of Enterprise Manager -> Management -> SQL Server Agent Properties -> the Job System tab
Best regards,
delpiero
July 28, 2005 at 5:11 pm
Damn those checkboxes !
The systems fine with no users loggged in. Can we keep it that way ?br>
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply