sql 2005/2008 change ownership of tables

Published by exdone

Posted on February 24, 2008

==========

After some research I found this posted online , works like a charm,

Do one thing, use the query below
select ‘Exec sp_changeobjectowner ”’ + name + ”’, ”dbo”’ from sysobjects where xtype = ‘U’

and copy all the results and execute them in a query analyser and it will change the tables owner, some which ar already having dbo as owner will give error but that doesnt affect anything.

or

Perhaps you need to create a new schema and transfer all objects into

SELECT ‘ALTER SCHEMA new_schema TRANSFER ‘ + SCHEMA_NAME(schema_id) + ‘.’ +
name
FROM sys.tables
WHERE schema_id = SCHEMA_ID(‘old_schema’);

SELECT ‘ALTER SCHEMA new_schema TRANSFER ‘ + SCHEMA_NAME(schema_id) + ‘.’ +
name
FROM sys.views
WHERE schema_id = SCHEMA_ID(‘old_schema’);

SELECT ‘ALTER SCHEMA new_schema TRANSFER ‘ + SCHEMA_NAME(schema_id) + ‘.’ +
name
FROM sys.procedures
WHERE schema_id = SCHEMA_ID(‘old_schema’);

http://www.eggheadcafe.com/conversation.aspx?messageid=33197831&threadid=33197805

=====================================================

How to change database schema (owner) to ‘dbo’ for multiple tables at once.
Thanks to http://www.lunarforums.com/lunarpages_windows_development_hosting/change_ms_sql_database_table_schema_owner-t41439.0.html

Tables

Instructions:

http://www.sqlservercentral.com/columnists/kKellenberger/understandingobjectownership.asp

Change the code below in red to your old schema name. It works like a find and replace. It will change everything to ‘dbo’ schema, or you can enter a new schema name.

This is a two-step process. Run this code in the query tool of My Little Admin. It will display the results. Copy and paste the results into a new query and run it again to change all the table schema names.

Code:

declare @OldOwner varchar(100)
declare @NewOwner varchar(100)

set @OldOwner = ‘OldOwner’
set @NewOwner = ‘dbo’

select ‘sp_changeobjectowner ”[‘ + table_schema + ‘].[‘ + table_name + ‘]”, ”’ + @NewOwner + ”’
go’
from information_schema.tables where Table_schema = @OldOwner

Stored Procedures

Instructions:

http://weblogs.asp.net/steveschofield/archive/2005/12/31/434280.aspx

Change the code below in red to your old schema name. It works like a find and replace. It will change everything to ‘dbo’ schema, or you can enter a new schema name.

This is a two-step process. Run this code in the query tool of My Little Admin. It will display the results. Copy and paste the results into a new query and run it again to change all the stored procedure schema names.

Code:

SELECT ‘ALTER SCHEMA dbo TRANSFER ‘ + s.Name + ‘.’ + p.Name FROM sys.Procedures p INNER JOIN
sys.Schemas s on p.schema_id = s.schema_id WHERE s.Name = ‘OldUsername’

Note: Be sure to go back and read through all of your stored procedures in detail. Anywhere that references a table with the old schema must be changed to the new schema. For example: bakeryUser.myTable should be renamed to dbo.myTable.

Functions

Instructions:

http://www.enterprisedb.com/documentation/sql-alterfunction.html

Code:

ALTER FUNCTION sqrt(integer) RENAME TO newFunctionName;
ALTER FUNCTION sqrt(integer) OWNER TO newUsername;

Or, since I only had a few you can do what I did. Just view the function and copy the code. Next, delete the function and then create a new function with the same name. Finally, paste the code and change the schema name.

Be sure to change the ALTER line of code that you copied to:
CREATE FUNCTION .

Here is an example:
CREATE FUNCTION dbo.FunctionName
….paste rest of code here

=================

exec sp_MSforeachtable “ALTER SCHEMA new_schema TRANSFER ? PRINT ‘? modified’ ”

http://xman892.blogspot.com/2007/02/sql-server-2005-change-schema-for-all.html

===========

Stored proc

SELECT ‘ALTER SCHEMA dbo TRANSFER ‘ + s.Name + ‘.’ + p.Name FROM sys.Procedures p INNER JOIN
sys.Schemas s on p.schema_id = s.schema_id WHERE s.Name = ‘OldUsername’

or try

DECLARE
@OldOwner sysname,
@NewOwner sysname

SET @OldOwner = ‘oldOwner’
SET @NewOwner = ‘dbo’

DECLARE CURS CURSOR FOR
SELECT
name
FROM
sysobjects
WHERE
type = ‘p’
AND
uid = (SELECT uid FROM sysusers WHERE name = @OldOwner)
AND
NOT name LIKE ‘dt%’ FOR READ ONLY

DECLARE @ProcName sysname

OPEN CURS

FETCH CURS INTO @ProcName

WHILE @@FETCH_STATUS = 0
BEGIN
IF @@VERSION >= ‘Microsoft SQL Server 2005’
BEGIN
EXEC(‘alter schema ‘ + @NewOwner + ‘ transfer ‘ + @OldOwner + ‘.’ + @ProcName)
exec(‘alter authorization on ‘ + @NewOwner + ‘.’ + @ProcName + ‘ to schema owner’)
END
ELSE
EXEC(‘sp_changeobjectowner ”’ + @OldOwner + ‘.’ + @ProcName + ”’, ”’ + @NewOwner + ””)

FETCH CURS INTO @ProcName
END

CLOSE CURS
DEALLOCATE CURS
============

another option

SELECT ‘ALTER SCHEMA oldname TRANSFER ‘ + s.Name + ‘.’ + o.Name
FROM sys.Objects o
INNER JOIN sys.Schemas s on o.schema_id = s.schema_id
WHERE s.Name = ‘newname’
And (o.Type = ‘U’ Or o.Type = ‘P’ Or o.Type = ‘V’)

note* if the tables are using reserved keywords you will need to use the brackets

ALTER SCHEMA testscheme TRANSFER meschem.[hello]

notepad++ redex

find \.(.*)

Replace  .[\1]

This will add the brackets in notepadd ++

Categories: SQL