Finding Owners of Objects 1

[ScottAB]

How do I find the owner of objects in a database? I want to remove a particular user from one database but I can't because this user owns some objects. I want to change the ownership of these objects and then remove the user. Does this sound valid?

 

[MeanGreen]

Yes it is valid. Use the sp_changeobjectowner to change ownership. I use a cursor to loop through the tables in sysobjects with owner_id and then run the stored procedure to change ownership.
An example to change ownership is:

EXEC sp_changeobjectowner 'authors', 'Corporate\GeorgeW'


Good Luck.

 

[MeanGreen]

Here is a loop to change all DBO owners to newowner. NEWOWNER must exist in the database for this to work.

declare @tablename varchar(50)
declare @sqlstr varchar(4000)

declare loop_var CURSOR for
select obj.name from sysobjects obj join sysusers usr
on obj.uid = usr.uid
where usr.name = 'dbo'

open loop_var

fetch next from loop_var into @tablename
while @@fetch_status = 0
begin
select @sqlstr = 'sp_changeobjectowner ''' + 'dbo.' + @tablename + ''' , ' + '''newowner'''
select @sqlstr -- change to EXEC (@sqlstr)
fetch next from loop_var into @tablename
select @sqlstr = ''
end
close loop_var
deallocate loop_var



Hope this helps!