How to change SSIS owner in 2005
I am trying to setup jobs in SQL 2005 to be most restrictive on security. One of the jobs runs an SSIS package. The problem occurs in that the package creator is not who runs the job. I also dont want to add them to the dtsoperator group because thats more permissions than needed. So what I want to do is change the owner of a package (like you can do with every other object in sql) to that user. This way they can only execute that package.
February 23rd, 2011 4:01pm

You are probably referring to the package protection level . The package was probably developed under the default EncryptSensitiveWithUserKey level and now does not run under the Agent? See this MSDN article discussing how we set/troubleshoot SSIS packages to run under SQL Server Agent: http://support.microsoft.com/kb/918760Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
February 23rd, 2011 4:09pm

The issue is not with the sensitive data inside the package. The issue is that you have to be a member of db_dtsadmin, db_dtsoperator, or the package creator to execute a package. So if "Developer A" created the package but the job is executed by "Job Account 1" then it has to be dtsadmin or dtsoperator since it was not the creator. However this will give it access to other packages. So I want to conver the creator/owner from Developer A to Job Account 1 so that it can only execute that specific package.
February 23rd, 2011 4:33pm

The creator is irrelevant, the package does not inherit Windows Security rights. You can set the protection level to Rely on Server Storage if you put it into MSDB. PS: DTUtil can override whatever the developer has it set using the /En option. Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
February 23rd, 2011 4:44pm

You can create a proxy account as described here: http://support.microsoft.com/kb/912911 (Method 2).Arthur My Blog
February 23rd, 2011 4:49pm

Like ArthurZ said, use SQL Agent to run your SSIS packages and setup proxy account in SQLServer to run the agent job.If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer. Sevengiants.com
Free Windows Admin Tool Kit Click here and download it now
February 24th, 2011 11:53am

Here is also an example of setting up a proxy account for scheduling a package. Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
February 24th, 2011 12:32pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics