» Fig Leaf Software Home

We've Got You Covered.

Monday, June 1, 2009

Change SQL Server Object Owners

When installing Paperthin's CommonSpot or Adobe Connect, you may accidently create a database schema where the table owner is not dbo. This can adversely affect the operation of these products.

The following transact SQL script can automatically change the table owners in a database back to dbo.


declare @ObjectName varchar(256)
  -- we are only interested in USER Objects
  -- not already owned by 'sa'
  -- we don't want keys and constrainst
  set @ObjectName = (
    select top 1 [name] from sysobjects
   where uid <> SUSER_SID('sa')
   and [type] in ('FN','IF','P','TF','U','V')
   )
 declare @ObjectOwner varchar(256)
 declare @ObjectFullName varchar(512)
 declare @NewOwner varchar(256)
   set @NewOwner = 'dbo'
  
   -- default to 'dbo' if null
   set @NewOwner = isnull(@NewOwner, 'dbo')
  
 while @ObjectName is not null
 begin
   select @ObjectOwner = USER_NAME(uid) 
     from sysobjects where [name] = @ObjectName
   set @ObjectFullName = @ObjectOwner + '.' + @Objectname
   PRINT 'Changing ownership of ''' + @Objectname + 
     ''' from ''' + @ObjectOwner + ''' to ''' + 
     @NewOwner + ''''
   execute sp_changeobjectowner @ObjectFullName, @NewOwner
   set @ObjectName = (select top 1 [name] from sysobjects
     where uid <> SUSER_SID('sa') 
     and [type] in ('FN','IF','P','TF','U','V'))
 end




Note: This script was originally penned by Geoff Appleby

No comments:

Post a Comment

About Us

Fig Leaf Software is an award-winning team of imaginative designers, innovative developers, experienced instructors, and insightful strategists.

For over 20 years, we’ve helped a diverse range of clients...

Read More

Contact Us

202-797-7711

Fig Leaf Software

1400 16th Street NW
Suite 450
Washington, DC 20036

info@figleaf.com