Published by exdone
Posted on February 24, 2008
DECLARE @old sysname, @new sysname, @sql varchar(1000) SELECT @old = 'OLDUSERNAME' , @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
changing stored procs
SELECT 'EXEC(''sp_changeobjectowner @objname = '''''+ ltrim(u.name) + '.' + ltrim(s.name) + '''''' + ', @newowner = dbo'')' FROM sysobjects s, sysusers u WHERE s.uid = u.uid AND u.name <> 'dbo' AND xtype in ('V', 'P', 'U') AND u.name not like 'INFORMATION%' order by s.nameThis query finds every view, stored procedure and user table in the database not owned by the dbo and converts ownership to the dbo. The output looks like this:
EXEC('sp_changeobjectowner @objname = ''lname.Authors'', @newowner = dbo') EXEC('sp_changeobjectowner @objname = ''lname.BANNER_Ads'', @newowner = dbo') EXEC('sp_changeobjectowner @objname = ''lname.Comments'', @newowner = dbo')You can simply copy and paste the EXEC statements into Query Analyzer and run it. This might have been overkill for fifteen objects in my database. At work I deal with a database that has over 6,000 stored procedures and approaches like this are a little more appropriate. Enjoy.