• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 908
  • Last Modified:

change owner of ssis package

Is there a way that you can change the owner of an ssis package in sql 2008 - preferably on the command line.
0
david_32
Asked:
david_32
  • 12
  • 12
1 Solution
 
rob_farleyCommented:
Two questions:
1/ What are you trying to achieve by doing this?
2/ Where does the package live (file system / MSDB)?

For example... if you're trying to make it so that someone else can see the secret info about a package, then changing the owner won't help.

Rob
0
 
david_32Author Commented:
I trying to change the owner of the package so that it complements the ProtectionLevel with EncyptSensitiveWithUserKey. The owner I want to change to will be a common user that all users can use to run jobs that can run any package a user has priv to run and will setup as a sql agent proxy. The developers don't want me to use Encrypt with password because it slows down the development time and and I am having trouble with ServerStorage because when I use dtutil it is telling me that  I cannot use Serverstorage because the msdb database it not at the right version. I think this is because I have sql 2005 and 2008 on the server and dtutil is getting mixed up on which msdb database it should use.

The package lives \MSDB\Data Collector.


thanks

0
 
rob_farleyCommented:
You should be able to tell dtutil which instance you need to talk to...
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
david_32Author Commented:
I am I run
dtutil /SourceServer <servername>/SQL <package> /ENCRYPT SQL;<package>;5 /Q

it does not work.
0
 
rob_farleyCommented:
Can you put the instance slash the other way around?

dtutil /SourceServer <servername>\SQL <package> /ENCRYPT SQL;<package>;5 /Q

And let me know what the error is?

Rob
0
 
david_32Author Commented:
/SQL  is a switch for dtutil to specify a location of the package that is why I put <package> afterwards.

Could not save package <packagename> because of error 0xC001401C.
Description: Storing or modifying packages in SQL Server requires the SSIS runti
me and database to be the same version. Storing packages in earlier versions is
not supported.

I have sql 2005 + 2008 on the box. I am not using ssis 2005 and I am running dtutil from the path of the 2008 binary




0
 
rob_farleyCommented:
Oh, sorry... I was thinking you meant the name of your instance was SQL, from there no being a space betwen <servername> and the slash.

Is your \DTS\Binn\msdtssvr.ini.xml pointing to the right MSDB database?

Rob
0
 
david_32Author Commented:
Yes it is .. I thought that was the answer but no luck :)
0
 
rob_farleyCommented:
Ok... so it's pointing to the 2008 instance?

And when you're in SQL Config Manager, you can see Integration Services 10.0?

Are you specifying the dtutil path explicitly? Like... .\dtutil.exe, not just dtutil (thinking that even though you're in the right folder, it may be preferring a different dtutil if there's one in the path).

Rob
0
 
david_32Author Commented:
yes .. I tried running dtutil both ways cd to the right folder and also running
<path>:\dtutil .......



0
 
rob_farleyCommented:
Can you stop all the 2005 services and see what happens?
0
 
david_32Author Commented:
actually I already tried that  and it did the same thing.
0
 
rob_farleyCommented:
Can you get the package out (using SSMS), and then use /FILE instead?
0
 
david_32Author Commented:
you can't use file package storage if you want to use the ServerStorage Protection level on the package (that is the 5 bit of /ENCRYPT SQL;<package>;5 in my dtutil command)
0
 
david_32Author Commented:
also it is not a solution for me as I want to store the packages in the database.
0
 
rob_farleyCommented:
Yeah - I was thinking maybe you could pull the package into the file system, make your changes, and then push them back in.
0
 
david_32Author Commented:
not a bad idea except I can't change the protectionlevel to serverstorage  when the package is a file - I tried it already.
0
 
rob_farleyCommented:
Are you doing this on the local server, or remotely?

(if remotely, can you try it on the local server)

Just that the error message about versions suggests that there's a discrepancy between client tool and server product somewhere.
0
 
david_32Author Commented:
on the local server. If you run the command remotely it will work as long as there is a 2008 SQL server also on the client machine.

0
 
rob_farleyCommented:
Really makes it sound like the dtutil process is for a different version...

I'm clutching at straws now.
0
 
david_32Author Commented:
I agree.. before this thread we were considering removing sql 2005 from the current problem box so that 2008 is by itself. I probably am going to take that course of action.
0
 
rob_farleyCommented:
Do you have another environment that just contains SQL 2008, that you could use to test it?
0
 
david_32Author Commented:
I have 2008 on my client machine .. I could probably test with that.
0
 
rob_farleyCommented:
Yeah - terrific. Let me know how it goes, and then we can move on with this.

Rob
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now