SP_CHANGEOBJECTOWNER
Change the owner of the object in the current database.
grammar
sp_changeObjectowner [@objname =] 'Object', [@newowner =] @newowner =] @Newer '
parameter
[@objname =] 'Object'
The name of existing tables, views, or stored procedures in the current database. The data type of Object is NVARCHAR (517) without default. Object can be defined by existing object owners, format is existing_owner.object.
[@newowner =] 'Owner'
The name of the security account that is about to become the new owner of the object. Owner's data type is sysname, no default. Owner must be a valid Microsoft® SQL ServerTM user or role or a Microsoft Windows NT® user or group in the current database. Specify a Windows NT user or group, specify a Windows NT user or group in the database (add SP_GRANTDBACCESS).
Returns the code value
0 (success) or 1 (failed)
Comment
Object owners (or members of the object's group or role) have special permissions to the object. The object owner can perform any Transact-SQL statements related to objects (such as INSERT, UPDATE, DELETE, SELECT, or EXECUTE), or manage objects permissions.
If you have an object's security account must be removed, but at the same time, you should reserve the object, use sp_changeObjectowner to change the object owner. This process removes all existing permissions from the object. After running sp_changeObjectowner, you need to re-apply any permissions to keep.
For this reason, it is recommended to write a script for existing permissions before running sp_changeObjectowner. Once the object's ownership is changed, you may need to re-apply the permissions using this script. You need to modify the object owner in the permission script before running the script. For more information on writing a database script, see Writing Database Documents and Scripts.
You can change the owner of the database using sp_changedbowner.
Authority
Only the sysadmin fixed server role and DB_OWNER fixed database role, or both a db_ddladmin fixed database role is a member of the DB_SecurityAdmin fixed database role to execute sp_changeObjectowner.
Example
The following example changes the owner of the Authors table to Corpoate / Georgew.
EXEC SP_CHANGEOBJECTOWNER 'Authors', 'Corporate / Georgew'
EXEC SP_CHANGEOBJECTOWNER 'YCHW.PROC_SUBJECT512_FUNCTION', 'DBO'
