Редактиране

Споделяне чрез

sp_changeobjectowner (Transact-SQL)

  • Статия

Applies to: SQL Server

Changes the owner of an object in the current database.

Important

This stored procedure only works with the objects available in SQL Server 2000 (8.x). This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER SCHEMA or ALTER AUTHORIZATION instead. sp_changeobjectowner changes both the schema and the owner. To preserve compatibility with earlier versions of SQL Server, 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.

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