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:
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 :)
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.
Post a Comment