SQL Object Ownership script

DECLARE @objname nvarchar(517),
@DynSQL nvarchar(4000)

DECLARE obj_cursor CURSOR FOR
SELECT name FROM sysobjects
WHERE Uid <> 1

OPEN obj_cursor
FETCH NEXT FROM obj_cursor
INTO @objname

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Setting ownership on ' + @objname
 
SET @DynSQL = 'sp_changeobjectowner ''CHANGETHISFORYOURUSERNAME.' + convert(varchar, @objname) + ''' , ''dbo'''

--PRINT @DynSQL

EXECUTE sp_executesql @DynSQL

FETCH NEXT FROM obj_cursor
INTO @objname

END

CLOSE obj_cursor
DEALLOCATE obj_cursor

  • 0 Users Found This Useful
Was this answer helpful?

Related Articles

Connection Strings

We're often asked how to create a connection string to a database.  In fact a variety of...

How do I Publish my Microsoft SQL server database

In order to publish your database to your hosting package, you need to use the SQL database...

SQL Enterprise Manager reports 'no items'

Run this script in query analyzer against the master db ===================copy everything below...

SQL db shrink code

--Select Count(name) from sysdatabases declare @db as varchar(100)set @db = 'db to be shrunk...

Why can't I connect to the SQL server using Enterprise Manager/Management Studio

SQL Enterprise Manager tries, by default, to connect via Named Pipes. As this cannot run across...