Skip to content

Latest commit

 

History

History
80 lines (56 loc) · 3.87 KB

sp-changeobjectowner-transact-sql.md

File metadata and controls

80 lines (56 loc) · 3.87 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
sp_changeobjectowner (Transact-SQL)
sp_changeobjectowner changes the owner of an object in the current database.
VanMSFT
vanto
randolphwest
08/22/2024
sql
system-objects
reference
sp_changeobjectowner_TSQL
sp_changeobjectowner
sp_changeobjectowner
TSQL

sp_changeobjectowner (Transact-SQL)

[!INCLUDE SQL Server]

Changes the owner of an object in the current database.

Important

This stored procedure only works with the objects available in [!INCLUDE ssVersion2000]. [!INCLUDE ssNoteDepFutureAvoid] Use ALTER SCHEMA or ALTER AUTHORIZATION instead. sp_changeobjectowner changes both the schema and the owner. To preserve compatibility with earlier versions of [!INCLUDE ssNoVersion], this stored procedure will only change object owners when both the current owner and the new owner own schemas that have the same name as their database user names.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

sp_changeobjectowner
    [ @objname = ] N'objname'
    , [ @newowner = ] N'newowner'
[ ; ]

Arguments

[ @objname = ] N'objname'

@objname is nvarchar(776), with no default.

The name of an existing table, view, user-defined function, or stored procedure in the current database. @objname is an nvarchar(776), with no default. @objname can be qualified with the owner of the existing object, in the form <existing_owner>.<object_name> if the schema and its owner have the same name.

[ @newowner = ] N'newowner'

The name of the security account that will be the new owner of the object. @newowner is sysname, with no default. @newowner must be a valid database user, server role, Windows user, or Windows group with access to the current database. If the new owner is a Windows user or Windows group for which there's no corresponding database-level principal, a database user is created.

Return code values

0 (success) or 1 (failure).

Remarks

sp_changeobjectowner removes all existing permissions from the object. You'll have to reapply any permissions that you want to keep after running sp_changeobjectowner. Therefore, we recommend that you script out existing permissions before running sp_changeobjectowner. After ownership of the object changes, you can use the script to reapply permissions. You must modify the object owner in the permissions script before running.

To change the owner of a securable, use ALTER AUTHORIZATION. To change a schema, use ALTER SCHEMA.

Permissions

Requires membership in the db_owner fixed database role, or membership in both the db_ddladmin fixed database role and the db_securityadmin fixed database role, and also CONTROL permission on the object.

Examples

The following example changes the owner of the authors table to Corporate\GeorgeW.

EXEC sp_changeobjectowner 'authors', 'Corporate\GeorgeW';
GO

Related content