March 19, 2004 at 7:29 am
Here is my problem. We have a few users who need to run a dts job at the end of the day to create the bank file. I have a stored procedure that does some prep work and then uses xp_cmdshell to create the file. The stored procedure is issued by an I/Q report.
It seems that we needed to make these users members of the domain admin group for them to be able to create this file.
What am I missing? How can I let users run DTS jobs without access to the server itself?
Any suggestions? Thanks, Barb
March 22, 2004 at 8:00 am
This was removed by the editor as SPAM
March 23, 2004 at 11:28 pm
Barbara,
Extended stored proc run in the context of the SQL Server agent account which will either be the sysadmin or the proxy account if you set SQL Server agent up that way.
Try re-evaluating your need for the xp_cmdshell. If you are using it to run an export application instead of a dts export task you might consider using package level owner and user level passwords. It seems like your need to write to the Sql Server OS Server disk is your security constraint and not the need to execute the task or the xp_cmdshell. Perhaps all that is needed is for users to be granted write access to the server shares where the file needs to be created and then have them run the job from DTSRUN or DTSRUNUI under there sql server login security context.
Here' a really relevent msdn link regarding the securing of DTS packages saved at the server.
http://msdn.microsoft.com/library/en-us/dtssql/dts_pkgmng_sec_09dl.asp
Peter Evans (__PETER Peter_)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply