DTS Permission requirements

[Catadmin]

I have a couple of DTS jobs that I scheduled & ran in a test lab under my domain login on the same server, one database to another. My domain login is mapped to the SysAdmin fixed server role on the test lab and SQL Server is using the Local System account.

When I tried the job on the Production server to another Production server, to which I do not have SysAdmin privledges, it failed. Also, I've been told both machines use the Local System account for SQL Server & SQL Server Agent.

It's been so long since I did DTS, and this is the first time I've done it from one machine to another, that I can't remember what the permissions required for DTS are. Can't seem to find the right thread in BOL either.

I read an older thread that the machines need to have SQL Server & SQL Server Agent using a domain account of some sort. Does it have to be an Administrator account, or can it be a normal user account? Is there a specific Server role that DTS needs to be enabled to run under?

Thanks,





Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[Catadmin]

Well, I was able to schedule a DTS package that ran successfully with SQL Server and SS Agent logged in as a regular domain user account with "Act as part of the Operating System" and "Logon as a Service" rights (not sure if both of those are needed). Used the same account on both machines. Mapped the Login to SQL Server, but removed all fixed server roles on both machines/DBs.

I'm assuming that the Act as a part of the OS right is overriding the lack of fixed server roles. If anyone has any ideas as to why a DTS job worked under a login that essentially has no permissions other than login permissions to SQL, I'd love to hear it.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!