Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kpsreenath
Yak Posting Veteran
52 Posts |
 Posted - 2006-04-11 : 11:37:24
|
| Hi In production, I have couple of DTS packages saved under the ownership of the old dba(her network id). All the new DTS packages have my network authentication id as the ownership.I read an article in the sqldts.com saying, this will not affect the day to day activities. Link belowhttp://www.sqldts.com/default.aspx?212My questions1) Is there anyway I can create the DTS package with an sql authentication id (sa) instead of the my windows authentication id.This will clear off the issue of person specific ownership for the dts package.2) I am aware of the undocumented stored procedure sp_reassign_dtspackageowner to change the owner name.Incase i am changing it for all the packages in the server, what will be the impact?Most of the dts packages are run using the jobs and i assume there wont be any problem with that.what about the packages directly invoked using the applicaiton?ThanksSree |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-04-11 : 12:06:00
|
| Bad practice I know, but what about updating sysdtspackages directly? I can't see there being a problem updating the packages if the old employee was a DBA and therefore had dbo rights, same as you should have.. |
 |
|
|
bakerjon
Posting Yak Master
145 Posts |
Posted - 2006-04-11 : 13:40:34
|
| There's nothing wrong with the undocumented sp_reassign_dtspackageowner proc. I have used it quite a bit in the past with no reprocussions.use msdbgoselect 'sp_reassign_dtspackageowner ''' + rtrim(name) + ''', ''' + rtrim(cast(id as char(1000))) + ''', ' + <NEW_USERNAME>''' + char(10) + 'go' from sysdtspackages where owner = <OLD_USERNAME>JonHolding it down on the engineering tip, y'all!http://www.sqljunkies.com/weblog/outerjoin |
|
|
|
kpsreenath
Yak Posting Veteran
52 Posts |
Posted - 2006-04-11 : 13:43:38
|
| Rick,I can update the owner(as i am the sysadmin)using the stored proc, but this doesnt solve the problem. Everytime i create a new dts package, it will have my network id in the owner. Looks like, to best of my knowledge, there is no way we can avoid the owner being person specific. As far as it doesnt create any problems in production, we look good.ThanksSree |
|
|
|
|
|
|
|
|
DTS package ownership issue