Search This Blog

Thursday, April 3, 2008

Change Table and Stored Procedure Ownership From xxx to DBO

Need to change the ownership for table and stored procedure

Script for change table ownership

DECLARE @old sysname, @new sysname, @sql varchar(1000)

SET @old = 'oldOwner'
SET @new = 'dbo'
SET @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


Credit should give to Scott Forsyth

change ownership for stored procedure

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

Credit should give to Greg Duffield









2 comments:

Anonymous said...

i genuinely enjoy all your writing choice, very interesting.
don't quit and also keep posting due to the fact it simply just that is worth to read it,
excited to look over much of your web content, stunning day :)

Anonymous said...

Good day!
My name is Tom,

I'm excited to be part of this large and growing forum of great people and thankyou all for making me feel welcome. I just joined today.

My special interests or skills are:
- HTML
- SEO
- Web design
- Internet business

I am happy to help others that need it and offer advice where possible :)

Look forward to 'meeting' you all.