ownership problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

ownership problem

Author

Topic

itsonlyme4
Posting Yak Master

109 Posts

Posted - 2008-01-04 : 15:44:35

Just started a job in a new shop and the DBA a'fore me created tons of DTS Packages leaving the owner as his NT Authenticated login. Before the AD guys delete the former DBA's AD account, I need to change ownership on these. I think I have a handle on that - found this script to change ownership (Actually I found 3:):::

--Change DTS Package owner
UPDATE msdb..sysdtspackages SET owner = 'sa' WHERE name = 'PackageName'

--OR 

DECLARE @SQL VARCHAR(1000)
SELECT @SQL = 'EXEC sp_reassign_dtspackageowner ''' + name + ''', ''' + CAST(id AS VARCHAR(10)) + ''', ''' + new_owner + ''''
FROM sysdtspackages
WHERE name = ' '
EXEC (@SQL)

--id is the ID of your DTS package


-- OR 

-- sp_reassign_dtspackageowner [@name =] 'name',
-- [@id =] 'id',
-- [@newloginname =] 'newloginname'

Is this just three different way to do that same thing in a SQL 2000 environment????

Also, not sure what account would be best for new owner.. sa? another sql login with sysadmin? an NT authenicated account with sysadmin? just wanted to know what would be a best practice.

AND... what else do I have to be concerned about? obviouslsy I'll have to check to see if his account has DB owner on any databases but is there a way to query a Database or a whole instance to see what objects might be owned by that account?

any help would be greatly appreciated

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-01-04 : 16:36:15

On ours we have dedicated permanent user id's for DTS jobs and agent jobs, which essentially have the necessary permissions.

On one of the other servers, all of the jobs are executed as 'sa'.

Check DTS packages to make sure he isn't the "owner" of those as well...

You can change any objects owned by that id using something like this:

SELECT 'EXEC(''sp_changeobjectowner @objname = '''''+
ltrim(u.name) + '.' + ltrim(s.name) + ''''''
+ ', @newowner = dbo'')'
FROM sysobjects s,
sysusers u
WHERE s.uid = u.uid
AND u.name <> 'dbo'
AND xtype in ('V', 'P', 'U')
AND u.name = 'username'
order by s.name

where the 'username' would be the departed party.

YOu can hook into the sysdtspackages tables as well using similar methodology to get the DTS ID's for packages owned by that person. I think the same might apply to sysjobs for the agent jobs...

For DTS packages...undocumented procedure:

sp_reassign_dtspackageowner @name='My Package name', @id='package id numbers', @newloginname='NewOwner'

Poor planning on your part does not constitute an emergency on my part.

donpolix
Yak Posting Veteran

97 Posts

Posted - 2008-01-05 : 13:45:00

I'd go to using sp_reassign_dtspackageowner which is easier than poking around sysdtspackages table.
Looks like only the DBA created and worked alone on the dts packages, so you may be well setting the ownership to any account with sysadmin server role, most likely your nt login.

Donn Policarpio

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-05 : 21:46:17

By the way, deleting owner's account from AD will not affect package.

itsonlyme4
Posting Yak Master

109 Posts

Posted - 2008-01-07 : 08:53:37

Sounds best then to reassign all the DTS Packages to a SQL login then like 'sa' or another sysadmin type SQL account?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-07 : 23:11:26

Owner should be windows account and doesn't need sysadmin rights.