DTS package cross over database access permission | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

DTS package cross over database access permission

Hi to all, I have existing DTS package that is updating emp table in G database,
and in the emp table, it has update triggers (uemp) to update emprole table in D database using sa user. Whenever I run the job, it end up in giving error message "access or permission denied in update trigger uemp line 15". (uemp trigger’s line 14 has T-SQL command to update D..emprole table). What I think, the update is happening in cross over database environment, DTS package is trying to access;update D database from G database, then it failed to do update because of permission problems. The sa user should have the permission to any databases in the server, but it fails I just hang around this error for a week trying hard to fix this problem.but no luck. Experienced guys, Please show some light on this problem. Thanks in advance. Jag
But if you have schedule that DTS package then it runs under context of SQLAgent account used, ensure that account has required privileges on the database(s). Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Thanks Satya for replying. Yes, job has the necessary permission in SQL Server Agent to run. But here the problem is DTS package update is spreading across more than one database, when it leaves the first specified database mentioned in connections, it gets error saying that access or permission denied. And there is new feature in SQl Server SP3 called Ownership chains. By enabling this service, I can able to do the stuffs, due to security concerns, I am not trying this on Production.
Any advise on this method or other one. I appreicate your reply. Thanks
Jag
Do not use the ownership chains as per the security notification, ensure the SQLAgent has required privileges to carry upon the tasks on the secondary server also. Fyi KBAhttp://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074 about scheduling DTS packages.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>