DB TAbLES
=========
DECLARE @old sysname, @new sysname, @sql varchar(1000)
SELECT
@old = ‘oldOwner_CHANGE_THIS’
, @new = ‘dbo’
, @sql = ‘
IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
WHERE
QUOTENAME(TABLE_SCHEMA)+”.”+QUOTENAME(TABLE_NAME) = ”?”
AND TABLE_SCHEMA = ”’ + @old + ”’
)
EXECUTE sp_changeobjectowner ”?”, ”’ + @new + ””
EXECUTE sp_MSforeachtable @sql
STORED PROCEDURES
=================
DECLARE @old sysname, @new sysname, @sql varchar(1000)
SELECT
@old = ‘oldOwner’
, @new = ‘dbo’
, @sql = ‘
IF EXISTS (SELECT NULL FROM information_schema.routines
WHERE
QUOTENAME(ROUTINE_SCHEMA)+”.”+QUOTENAME(ROUTINE_NAME) = ”?”
AND ROUTINE_SCHEMA = ”’ + @old + ”’
)
EXECUTE sp_changeobjectowner ”?”, ”’ + @new + ””
EXECUTE sp_MSforeachtable @sql
———————————————————————————–
Filed under: Sqlserver | Tagged: change, ownership, SQL server | Leave a comment »