October 25, 2005 at 2:15 am
I have a lot of developers in my IT. And they have created a lot of DTS packages. So, they have problem to change smth in packages of theirs colleges because they are not a member of sysadmin role and they are not owners of those packages.
I decided to create one login (for example 'dts') for this purpose, and to change all owners to new.
I use undocumented proc sp_reassign_dtspackageowner and it changed owner and owner_sid in msdb..sysdtspackages to 'dts'.
But .. When I logon in EM with new account ('dts'), and make change on some existing package, it make new row in sysdtspackages but with owner which is different (my domain account) and with owner_sid which is SUSER_SID('dts').
I don't want that. I want everything to be 'dts'.
Any help ?
October 26, 2005 at 8:39 am
In my shop we monitor dts creation. Developers can create all the DTSs they want, but if they want to modify a package that they are not the owner of, they have to do a 'save as' and create their own copy of the package, make their mods to it, and then have the DBAs review it..DBAs approve it and then make the mods to the original package (which is owned by the DBAs). In this way we treat DTSs just like a proc or a function...developers are free to make mods, but it doesn't get promoted or scheduled until the DBA approves it.
October 26, 2005 at 3:21 pm
Same situation here. sp_reassign_dtspackageowner and other workarounds aren't "sticky". You can change the owner of a package, but next time it gets saved, it reverts back to the original owner. We're implementing a procedure similar to LS's. When the DTS is ready for production, the DBA will login under a "DTS" account and do a save-as, so the DTS package shows the "dts" owner.
October 27, 2005 at 2:09 am
thanks for suggestions.
I have another question about it. How could I create DTS package and standar login to be owner? I could connect to EM with any login, but when I create package, and save it, my Windows domain account become owner
October 27, 2005 at 7:46 am
I think you'll have to log on to your machine as the other user.
October 27, 2005 at 8:01 am
You mean there is no way standard login to be owner.
For example 'dts'. It must be windows user ?
October 27, 2005 at 1:24 pm
I think so
October 27, 2005 at 2:01 pm
What I do is open EM using "Run As". This allows you to run an instance of EM as a different Windows account without logging off your machine or closing any applications. Use <SHIFT> right click in W2000, and just right click in XP (I think)
November 8, 2005 at 8:35 am
have you though of using sql logins?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply