changing owner for all tables on all databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

changing owner for all tables on all databases

Author

Topic

alpoor
Starting Member

29 Posts

Posted - 2007-02-09 : 13:50:05

What I was trying to see whether I can change ownership of all tables in all databases on the same server. I found the following script on internet. The problem here is how can I make this script work in all databases by submitting one shot

DECLARE @oldowner sysname, @newowner sysname, @sql varchar(1000)

SELECT
@oldowner = 'OLD_OWNER',
@newowner = 'dbo',
@sql = '
IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
WHERE
QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
AND TABLE_SCHEMA = ''' + @oldowner + '''
)
EXECUTE sp_changeobjectowner ''?'', ''' + @newowner + ''''

EXECUTE sp_MSforeachtable @sql

Taurkon
Starting Member

26 Posts

Posted - 2007-02-09 : 14:14:49

Do you mean databases or tables? I use this to update all my tables:

declare @CurrentOwner varchar(50)

-- set this variable to the current owner of the database objects you want to change to owner 'dbo'
set @CurrentOwner = 'whatever'

declare @objName varchar(500)

set @CurrentOwner = @CurrentOwner + 'dbo.'

DECLARE OwnerName CURSOR FOR
-- xtype 'U' is for tables, 'V' is for views.
SELECT name FROM SysObjects where xtype in ('U', 'V') order by name

OPEN OwnerName

FETCH NEXT FROM OwnerName into @objName

WHILE @@FETCH_STATUS = 0
BEGIN
set @objName = @CurrentOwner + @objName
exec sp_changeobjectowner @objName, 'dbo'
FETCH NEXT FROM OwnerName into @objName
END

CLOSE OwnerName
DEALLOCATE OwnerName

alpoor
Starting Member

29 Posts

Posted - 2007-02-09 : 15:53:31

I made it work on a single database. But,I have several databases with same tables.
I need to run this script once so that it changes all tables in all databases

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources