Why do the DTS packages think I own them?
-
We have recently transfered some DTS packages from one server to another as the databases are moving. We saved the packages individually as structured storage files then opened them on the new server and saved them as SQL Server local packages.
The packages were saved as 'sa' as the owner with the correct password. However when the packages are viewed through Enterprise Manager the owner is my personal login. I don't have a personal login on this server.
I login to the network with my personal login but do all work on the servers (through Enterprise Manager) as 'sa'; all our servers are registered with SQL Server authenication.
How did SQL Server get my personal login as the owner of these DTS packages and how can we change it?
Madame Artois
-
Yes, that field would more logically read Creator or Author rather than Owner. Has no relationship to how you login to SQL Server just how you are logged into the Windows machine you are on.
-
We have found the undocumented stored procedures in msdb called sp_reassign_dtspackage owner. It has three parameters of 'name', 'id' and 'new name'.
I have run this with the correct names and dts package name but got the error
Server: Msg 8114, Level 16, State 4, Procedure sp_reassign_dtspackageowner, Line 0
Error converting data type varchar to uniqueidentifier.
Has anyone got any clues!!
Madame Artois
-
sp_reassign_dtspackageowner must have all three parameters. you can find the ID by running sp_enum_dtspackages in msdb.
Greg
Greg
-
Further delving in the woodwork has revealed that sp_reassign_dtspackageowner requires 3 parameters. @Name being the name of the dts package, @id being the unique identifier (you can obtain this from sysdtspackages in msdb) and @newloginname which is Domain\Username (NT integrated) or SQL Server login.
Since the developer who wrote the original package (on a server where he left the 'sa' password blank) has long since gone and used an NT integrated password, we are still unpicking his work. We are going to try a generic NT login and use the same as a SQL Server login then run sp_reassign_dtspackageowner again.
Has anyone ever tried this?
Madame Artois
-
Logging on to the network as a generic NT login worked and we could then change the owner of the dts packages. However, to do any work on these transfered dts packages required the programmers to login as the generic NT login. New packages can be created, changed or deleted as a generic SQL login without the generic NT login. So somewhere in these transfered dts packages is the original programmers id and permissions.
Anyway we have decided to re-write the dts packages on the new server with the generic SQL server login. Its more work but solves future problems.
Thanks to everyone who read this post and replied.
Madame Artois
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply