Can only members of sysadmin schedule DTS jobs?

Question

Post reply

Can only members of sysadmin schedule DTS jobs?

Matthew Boorstin

SSC Enthusiast

Points: 143
More actions
March 9, 2005 at 9:08 am

#59997

Greetings all,
This is an offshoot question from one of my earlier posts. I can't get any of my DTS jobs to run if I schedule them. A number of posts have suggested that I get sysadmin rights in order for my logon to have the ability to run "xp_cmdshell".
However, I can't get these rights because it is not my server. Can only members of sysadmin schedule DTS jobs? Does this mean I'm out of luck?
I can't believe I'm the only person in the world who has had to schedule a job on a server he didn't own. Can someone PLEASE clue me in as to what needs to be done to allow my logon to schedule jobs? I'm desperate!!!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

Matthew,

Is the problem that you can't create the job or that you can't start the job once it's created?

Can you describe what your DTS package is doing? Does it import from or export to a location outside of SQL Server? As I stated in your other thread, scheduled DTS packages are run by the login assigned to SQL Server Agent, which must have permissions to access those locations.

Greg

XuJohn Say Hey Kid Points: 695 More actions · Answer 2

In this case, you may configure the proxy account in SQL server agent.

Matthew Boorstin SSC Enthusiast Points: 143 More actions · Answer 3

Greg, I can create, edit, and execute jobs manually (though when I execute a job manually, it requires that my local machine has the resources the package needs, i.e. CDONTS and an SMTP server. Why does a package on a remote server need resources on my local machine? But that's another question...)

The problem is that when I schedule it, it fails at it's scheduled time.

I found this:

http://www.novicksoftware.com/Articles/SQL-Server-2000-SP3-and-xp_cmdshell-Woes.htm

I am hoping desperately that this is the problem, because it is describing something very similar to my situation. It also mentions doing what XuJohn has suggested above.

I'll let you know what my administrators have to say...

Greg Charles SSC-Forever Points: 45403 More actions · Answer 4

I hope that works for you. I suspect that if your administrators won't make you a member of sysadmin, they might not want to create a proxy account to let you run xp_cmdshell either. Good luck!

I don't know what your working relationship is with the administrators, but maybe you can work something out with them so that they schedule the package. Developers can create DTS packages in my shop, but the DBAs schedule and monitor jobs.

BTW, unless you're connecting to the server via Terminal Services, the DTS package will run on your workstation rather than the server. That's why it requires the resources on your local machine. When a package is scheduled, it runs on the database server and therefore requires the same resources on that machine.

Greg