Changing all DB object owners to DBO

Changing all DB object owners to DBO

Post by Wayne Fielde » Thu, 22 Mar 2001 06:53:17



Greets all,

    We recently imported a database from another organization and noticed
that they had all kinds of users listed as owners for the various objects.
I need to change the ownership for every table to dbo.  I am very new to SQL
Server so there maybe a stored procedure out there that will do this for me.
I have looked at sp_changedbowner and sp_changeobjectowner but I can't
figure out how to tell this procedure to work on ALL the tables.  hints?

Wayne Fielder

 
 
 

Changing all DB object owners to DBO

Post by Wayne Fielde » Thu, 22 Mar 2001 07:39:23


After getting a good backup, I ran this code for every table.  My developer
won't be in for a while so I have time to restore the backup if need
be....did I*up too badly?

exec sp_changeobjectowner 'jsulliva.tblInstitutions', 'dbo'


Quote:> Greets all,

>     We recently imported a database from another organization and noticed
> that they had all kinds of users listed as owners for the various objects.
> I need to change the ownership for every table to dbo.  I am very new to
SQL
> Server so there maybe a stored procedure out there that will do this for
me.
> I have looked at sp_changedbowner and sp_changeobjectowner but I can't
> figure out how to tell this procedure to work on ALL the tables.  hints?

> Wayne Fielder


 
 
 

Changing all DB object owners to DBO

Post by Richard Waymir » Thu, 22 Mar 2001 10:10:04


no, this looks like the right thing to do.

--
Richard Waymire, MCSE, MCDBA

> After getting a good backup, I ran this code for every table.  My
developer
> won't be in for a while so I have time to restore the backup if need
> be....did I*up too badly?

> exec sp_changeobjectowner 'jsulliva.tblInstitutions', 'dbo'



> > Greets all,

> >     We recently imported a database from another organization and
noticed
> > that they had all kinds of users listed as owners for the various
objects.
> > I need to change the ownership for every table to dbo.  I am very new to
> SQL
> > Server so there maybe a stored procedure out there that will do this for
> me.
> > I have looked at sp_changedbowner and sp_changeobjectowner but I can't
> > figure out how to tell this procedure to work on ALL the tables.  hints?

> > Wayne Fielder

 
 
 

1. Setting DBO as owner for all objects in DB

Have a database where different users have been creating tables, and I want
to collect all of them under 'dbo' ownership.

Can the following TSQL be trusted to do the job?

        use MYDB
        go
        sp_configure 'allow updates', 1
        go
        reconfigure with override
        go
        update sysobjects set uid=1 where uid<>1
        go
        sp_configure 'allow updates', 0
        go
        reconfigure with override
        go

I've tried it on a small test case, and it seems to work, but are there any
gotchas?

Erik Oxaal

2. timeout during delete records

3. Change DB owner/Table owner

4. Outer join issues

5. How to change database owner from dbo

6. URGENT ! Help on BDE

7. user creating database object with dbo as owner

8. Sort Recordset

9. Object owner dbo (without 'sa')?

10. List non-dbo object owner's

11. Changing owner beyond DBO

12. Non dbo object owner

13. Help - Change Owner/dbo of Database