Hi:

How do you change the "owner" in a DTS package and a
scheduled job?

After my organization migrated all user signons to a new
Active Directory domain, the domain sign on to the PC
changed. Since we are using Windows authentication for
SQL server security, I no longer can change my existing
DTS package, and see my existing scheduled job for I am
now considered a different owner eventhough my user name
is the same.

PLEASE HELP!

In article <0b7d01c3a31b$f51c8f80$a001280a (AT) phx (DOT) gbl>, Steven Wong
<anonymous (AT) discussions (DOT) microsoft.com> writes
To change the owner a jobs you can use Enterprise
Manager, or the SQL DMO object library, or the sp_update_job stored
procedure. Personally I would use the latter in a T-SQL WHILE loop to
fix a lot of jobs.

The only way to change a package owner is to create a new package, for
example via Save As.

Package Ownership Issues
(http://www.sqldts.com/default.aspx?212)


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Darren:

First of thanks for the reply...it really helps.

Secondly, I tried out the 'spDTSChangeOwner' script and it
worked. But as your article says, the owner resort back
to the creator of the package when I saved it the next
time.

So, my question is...what is the point of changing the
owner than?

In article <0ece01c3a3ec$98984070$a001280a (AT) phx (DOT) gbl>, Steven Wong
<anonymous (AT) discussions (DOT) microsoft.com> writes
It allows you to do a one off change if required.

Personally I don't bother, but seem people have expressed a desire to do
so in the past so I provided a simple script.


----- Unsupported - Disclaimer etc, but it works for me ----

I normally rip out the sysadmin check from the stored procedures on Dev
boxes since it is inappropriate in those scenarios.

I have even replaced it with my own mechanisms, using a table that
defines user access to specific packages. Useful for boxes where you
wish to allow limited access to some people but not open completely.

Conversely I normally rip out the guest login from msdb on production
boxes as part of what we term hardening.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org