Change object owner

Under normal usage, object owner assignment is not a problem at all. However, it is possible to screw up permission/owner setting if you migrate your forum to another host.

The attached stored procedure will help correct this issue, provided that you have sufficient permission on the database. This stored procedure, spxChangeOwner, uses a system stored procedure, sp_changeobjectowner, and therefore, you have to make sure you have sufficient permission to execute sp_changeobjectowner before using spxChangeOwner.

The following is quoted from SQL Server Books Online:

Only members of sysadmin fixed server role, the db_owner fixed database role, or a member of both the db_ddladmin and db_securityadmin fixed database roles can execute sp_changeobjectowner.

If you somehow don't have permission to execute sp_changeobjectowner, we cannot help you set the permission on the server - you will have to ask your ISP to set the permission. SQL Server permission issue is out of my control.

Please read the instruction below on how to use the stored procedure, its limitations, and all of following notes before using it.

• Setup
  
  • Run the SQL script against your database
• Usage:
  
  • exec spxChangeOwner '[desired owner name]'
• NOTE:
  
  • spxChangeOwner takes one parameter only
  • [desired owner name] is the name of the owner (user alias) you want for ALL your objects in the current database(tables, stored procs, views)
  • You will see warnings after running the stored procedure: Caution: Changing any part of an object name could break scripts and stored procedures. Don't worry about this at all. This just tells you that this sp works!
  • For example, if I run exec spxChangeOwner 'dbo' , I will change the owner of ALL objects to 'dbo'
  • By ALL objects I mean:
    
    • tables prefixed with pgd_
    • stored procedures prefixed with sp
    • views prefixed with v_
    • It is therefore possible to affect objects you created outside the software (for whatever reason), if you use the same prefix scheme we use in the software.
    • Remember, every single one of your database object which uses the same prefix scheme will be affected! This is not limited to objects created by our software only
• Support
  
  • No support will be provided for this stored procedure. It is therefore mandatory to back up your database before using this stored procedure
  • The source code is also available, and therefore it is up to you to make changes to the source.
  • Disclaimer: THIS SOFTWARE AND THE ACCOMPANYING INSTRUCTIONS ARE PROVIDED "AS IS" AND WITHOUT WARRANTIES AS TO PERFORMANCE OR ANY OTHER WARRANTIES WHETHER EXPRESSED OR IMPLIED.
  • If your forum is running just fine, don't do anything to your database.

If you have read every single word above and have agreed to ALL the terms I list up there, click on the attachment link to download the script. 

Attachment(s)

changeowner.sql (0.69 KB) - downloaded 336 times