|
sp_changeobjectowner
Change the owner of the object in the current database.
grammar
sp_changeobjectowner [@objname =]'object', [@newowner =]'owner'
parameter
[@objname =]'object'
The name of an existing table, view, or stored procedure in the current database. The data type of object is nvarchar(517), and there is no default value. object can be qualified by the existing object owner, the format is existing_owner.object.
[@newowner =]'owner'
The name of the security account of the new owner who will soon become the target. The data type of owner is sysname, and there is no default value. The owner must be a valid Microsoft® SQL Server™ user or role or Microsoft Windows NT® user or group in the current database. When specifying a Windows NT user or group, specify the name of the Windows NT user or group known in the database (added with sp_grantdbaccess).
Return code value
0 (success) or 1 (failure)
Annotate
The object owner (or a member of the group or role that owns the object) has special permissions on the object. The object owner can execute any Transact-SQL statements related to the object (such as INSERT, UPDATE, DELETE, SELECT, or EXECUTE), and can also manage the permissions of the object.
If the security account that owns the object must be removed, but at the same time you want to keep 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 reapply any permissions you want to retain.
For this reason, it is recommended to write scripts for existing permissions before running sp_changeobjectowner. Once the ownership of the object is changed, the script may be used to reapply permissions. Before running the script, you need to modify the object owner in the permissions script. For more information about writing database scripts, see Writing database documentation and scripts.
You can use sp_changedbowner to change the owner of the database.
Authority
Only members of the sysadmin fixed server role and db_owner fixed database role, or both the db_ddladmin fixed database role and the db_securityadmin fixed database role, can execute sp_changeobjectowner.
Examples
The following example changes the owner of the authors table to Corporate\GeorgeW.
EXEC sp_changeobjectowner'authors','Corporate\GeorgeW' |
|