CODEWITHSUNDEEP
sqlsql-serverdatabasestored-procedures
Farmer
Farmer

Reputation: 10993

DBOwner to another SQL Server User

How can I change the owner of all the tables and stored procedures in an SQL Server 2000 Database.

I think "sp_changeobjectowner" can do it, but I can't find a way to achieve this.

can anyone help me ?

EDIT

declare
@old_owner char(45),
@new_owner char(45)

set @old_owner = 'dbo'
set @new_owner = 'mynewuser'

select 'exec sp_changeobjectowner ''' + replace(@old_owner,' ','') + '.' + t1.name + '''' + char(44) + char(32) + '' + @new_owner + '' + char(13) 
from sysobjects t1, sysusers t2
where t1.uid = t2.uid
and t1.name not like 'sys%'
and t2.uid <> 7
and t2.name = @old_owner

Upvotes: 2

Views: 253

Answers (2)

jim31415
jim31415

Reputation: 8808

Make sure the new owner is a user in that database.

You may need to preface the user name with the domain name: 'domain\username'.

use Northwind 
exec sp_MSforeachtable @command1="sp_changeobjectowner '?', 'domain\username'"

Or maybe try using a cursor if you need to only change owners based on some criteria:

DECLARE tables CURSOR
FOR
    SELECT [name]
      FROM sysobjects
     WHERE xtype = 'u'

OPEN tables
DECLARE @TableName NVARCHAR(512)
FETCH NEXT FROM tables INTO @TableName

WHILE @@fetch_status = 0
BEGIN

    EXEC sp_changeobjectowner @TableName, 'dbo'

    FETCH NEXT FROM tables INTO @TableName
END
CLOSE tables
DEALLOCATE tables

Upvotes: 0

Tom H
Tom H

Reputation: 47372

The query that you have will return a result set that has all of the commands that you will need, but you still need to actually run those commands.

Copy the results, paste them into a query window, and try running them.

Upvotes: 1

Related Questions