Sqlserver Modify Table owner

Batch modification:
Exec sp_msforeachtable 'exec sp_changeobjectowner ''? '', ''Dbo '''
Single modification:
Exec sp_changeobjectowner 'name of the table to be changed ', 'dbo'

Only the owner can change the table owner.

Very server: Message 15001, level 16, status 1, process sp_changeobjectowner, row 38
The object 'user' does not exist or is not valid for this operation.
Multiple tables are imported. Some owners do not remember the passwords.
I think it's okay if I didn't have the original owner!
Exec sp_changeobjectowner 'table owner. Name of the table to be changed ', 'dbo'
The table owner is not required to modify the table.

 

 

 

Supplement:

-- Execute this statement to change the owner of all tables in the current database to DBO.
Exec sp_msforeachtable 'SP _ changeobjectowner ''? '', ''Dbo '''

-- If you want to modify tables, stored procedures, views, triggers, and user-defined functions together, use a cursor (ignore the error prompt)
Declare TB cursor local
Select 'SP _ changeobjectowner '[' + Replace (user_name (UID), ']', ']') + ']. ['
+ Replace (name, ']', ']') + '] '', ''dbo '''
From sysobjects
Where xtype in ('U', 'V', 'P', 'tr', 'fn ', 'if', 'tf') and status> = 0
Open TB
Declare @ s nvarchar (4000)
Fetch TB into @ s
While @ fetch_status = 0
Begin
Exec (@ s)
Fetch TB into @ s
End
Close TB
Deallocate TB
Go

1. sp_changeobjectowner
Change the owner of objects in the current database.

Syntax
Sp_changeobjectowner [@ objname =] 'object', [@ newowner =] 'owner'

Parameters
[@ Objname =] 'object'

Name of the existing table, view, or stored procedure in the current database. The object data type is nvarchar (517), and no default value is available. The object can be defined by the existing object owner. The format is existing_owner.object.

[@ Newowner =] 'owner'

Name of the security account of the new owner of the object. The owner's data type 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. When specifying a Windows NT user or group, specify the name known to the Windows NT user or group in the database (use sp_grantdbaccess to add ).

ReturnCodeValue
0 (successful) or 1 (failed)

Note
The object owner (or a member of a 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), or manage the object permissions.

Use sp_changeobjectowner to change the object owner if the security account that owns the object must be removed but the object must be retained. This process deletes all existing permissions from the object. After running sp_changeobjectowner, you must re-apply any permissions you want to retain.

For this reason, we recommend that you write scripts with existing permissions before running sp_changeobjectowner. Once the ownership of the object is changed, you may need to re-apply the permission using this script. Before running the script, you must modify the object owner in the permission script. For more information about writing database scripts, see writing database documents and scripts.

You can use sp_changedbowner to change the database owner.

Permission
Sp_changeobjectowner can be executed only when the SysAdmin fixed server role and db_owner fixed database role are members, both db_ddladmin and db_securityadmin fixed database roles.

Example
In the following example, change the owner of the authors table to revoke ATE \ georgew.

Exec sp_changeobjectowner 'authors ', 'login ATE \ georgew'

See
Change database owner: sp_changedbowner

------------------------------ The above is from the SQL Server online series
Note:
When using sqlserver2000 to change the owner of an object (such as table, SP, view), note the following:
If the owner of the object changes to DBO, use the: exec sp_changeobjectowner object name directly. The new owner name can be used successfully. To change the owner of the object, use the following syntax:
Exec sp_changeobjectowner "[owner]. [Object Name]", new owner. Note: Double quotation marks cannot be omitted. Otherwise, the objectname does not exist prompt is displayed.

Exec sp_changeobjectowner 'cqadmin. Authors ', 111

 

2. Create a table with the same structure as the original table, and then insert the data from the original table to the new table. The operation is as follows:

Right-click the source table in the SQL Server Enterprise Manager, select "all tasks/survival SQL scripts", save the generated script, and change the original table owner to the new owner in the script. Then run the script again, and a new table is generated in the database. The owner is the new owner, and the original table is not replaced in the database. The new table and old table have the same name and different owner. At this time, the new table has no data.

Then run the following statement to insert data to the new table:

Insert into cqadmin. m_actiondef // new owner. New table name
Select *
From regaltecdefschema. cqadministrator. m_actiondef // database name. old owner. old table name
You can delete or save the old table.