Đây là thủ tục đổi owner các object từ một user khác về dbo.
create procedure dbo.changowner
@oldOwnerName varchar(100)
as
begin
declare @olduid int
select @olduid = uid from sysusers where name = @oldOwnerName
if @olduid is null
goto EXIT_PROC
declare object_cur cursor for
select @oldOwnerName + '.' + name from sysobjects where uid=@olduid
declare @name varchar(500)
open object_cur
-- Perform the first fetch.
fetch next from object_cur into @name
while @@FETCH_STATUS = 0
begin
exec sp_changeobjectowner @name, 'dbo'
fetch next from object_cur into @name
end
close object_cur
deallocate object_cur
EXIT_PROC:
end
Sử dụng thủ tục:
exec changowner 'nota'