Posted on 2 Comments

Change database owner in SQL server
(single and multiple items)

Changing a single table owner:

Exec sp_changeobjectowner ‘dbo12323.my_table’,’dbo’

dbo12323 = make this whatever the table currently has as the db owner
dbo = the new db owner

Changing multiple items owners:

SELECT * from sysobjects where uid = user_id(‘UseNAme’)
declare @Return int
exec @Return = sp_configure ‘allow updates’, ‘1’
SELECT @Return as ‘Returned Code’
GO
reconfigure WITH OVERRIDE
GO
DECLARE @Rows int, @Error int
BEGIN TRANSACTION
update sysobjects set uid = user_id(‘dbo’) where uid = user_id(‘UseNAme’)
SELECT @Error = @@Error, @Rows = @@RowCount
SELECT @Rows as ‘#Rows’
IF @Rows > 0
BEGIN
SELECT @Rows AS ‘#Rows’
COMMIT TRANSACTION
END
else
BEGIN
SELECT @Error AS ‘Error #’
ROLLBACK TRANSACTION
END

exec sp_configure ‘allow updates’, ‘0’
reconfigure WITH OVERRIDE
go

2 thoughts on “Change database owner in SQL server
(single and multiple items)

  1. In 2005 and newer you can use:

    sp_MSforeachtable @command1=”EXEC sp_changeobjectowner ‘?’,’dbo'”

  2. good, tnx

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.