change ownership of objects

A co-worker recently quit the company, and all of his stored
procedures and DTS packages are apparently under his name. Is there
any way to change his account to another (ie administrator or SA)'
I am afraid to delete his account in fear all the stored procedures
will be lost. (we have backups, but ... ugh)
(running mssql server 2000)Hello,
Fiirst of all change the password for his account to a one with special
charecters, so as no one can access. After that you could use
sp_changeobjectowner system proc to change the owner of all object from that
user to DBO. take
alook into sp_changeobjectowner in books online..Once you completed the
object owner to DBO you could drop the user and
login associated.
Thanks
Hari
"berwiki" <steveberwick@.gmail.com> wrote in message
news:1171656219.472511.150980@.m58g2000cwm.googlegroups.com...
>A co-worker recently quit the company, and all of his stored
> procedures and DTS packages are apparently under his name. Is there
> any way to change his account to another (ie administrator or SA)'
> I am afraid to delete his account in fear all the stored procedures
> will be lost. (we have backups, but ... ugh)
>
> (running mssql server 2000)
>|||To add on to my previous post to change DTS owner use:-
use msdb
go
sp_reassign_dtspackageowner [@.name =] 'name',[@.id =] 'id',[@.newl
oginname =]
'newloginname'
Thanks
Hari
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:...
> Hello,
> Fiirst of all change the password for his account to a one with special
> charecters, so as no one can access. After that you could use
> sp_changeobjectowner system proc to change the owner of all object from
> that user to DBO. take
> alook into sp_changeobjectowner in books online..Once you completed the
> object owner to DBO you could drop the user and
> login associated.
> Thanks
> Hari
> "berwiki" <steveberwick@.gmail.com> wrote in message
> news:1171656219.472511.150980@.m58g2000cwm.googlegroups.com...
>|||To add on Hari's reply
Run the output in the QA
SELECT 'EXEC sp_changeobjectowner '''+ROUTINE_NAME+''',''dbo'''
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+
'.'+ROUTINE_NAME),
'IsMsShipped')=0
AND ROUTINE_SCHEMA != 'dbo'
"berwiki" <steveberwick@.gmail.com> wrote in message
news:1171656219.472511.150980@.m58g2000cwm.googlegroups.com...
>A co-worker recently quit the company, and all of his stored
> procedures and DTS packages are apparently under his name. Is there
> any way to change his account to another (ie administrator or SA)'
> I am afraid to delete his account in fear all the stored procedures
> will be lost. (we have backups, but ... ugh)
>
> (running mssql server 2000)
>

Change Object Ownership

A co-worker recently quit the company, and all of his stored
procedures and DTS packages are apparently under his name. Is there
any way to change his account to another (ie administrator or SA)??

I am afraid to delete his account in fear all the stored procedures
will be lost. (we have backups, but ... ugh)berwiki (steveberwick@.gmail.com) writes:

Quote:

Originally Posted by

A co-worker recently quit the company, and all of his stored
procedures and DTS packages are apparently under his name. Is there
any way to change his account to another (ie administrator or SA)??
>
I am afraid to delete his account in fear all the stored procedures
will be lost. (we have backups, but ... ugh)

For the stored procedures do:

SELECT 'EXEC sp_changeobjectowner ''' + name + ''', '''dbo''''
FROM sysobjects
WHERE uid = USER_ID('co-worker')

copy, paste and run result set. This will take care of stored procedures,
tables etc. Note that any references to these procedures from elsewhere will
need to be updated.

I have never worked with DYS, so I cannot answer that part.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx