February 10, 2006 at 1:37 pm
I have a developer that is trying to execute a DTS package through a stored procedure that is erroring out on executing sp_OACreate. Is there any problem with granting him execute permission on that procedure? Here is the error:
This is the stored proc with parameters exect I am running it through Coldfusion code.
exec spExecuteDTS 'GAWIN-SQLE-S01\DW', 'Revenue Share New Customer Upload Development', 'nat1rwc05'
And this is the error I am getting.
Server: Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 19
EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.
February 10, 2006 at 7:08 pm
sp_OAcreate can only be executed by sysadmins.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
February 13, 2006 at 5:59 am
Why don't you try DTSRUN.......
Andy.
February 13, 2006 at 1:31 pm
Unfortunately, that too requires sysadmin rights to run because you have to do it through xp_cmdshell, unless you set up a proxy account...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
February 15, 2006 at 7:59 am
You can set up a proxy account for SQL Server Agent, try the links below for how. I know it works I have helped a lot of developers with it. If you have more question post again. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply