Change the owner of an SQL table

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 & reg; SQL Server user or role or Microsoft Windows NT & reg; user or group in the current database. 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 ).

Return code value
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'

Top
The second floor of milk520 (gorgeous-no free lunch) was replied to 14:37:30 score 0
Thank you, upstairs.

How to retrieve all tables?

Top
Shuiniu (flying dream) (I am a tomato-only buffalo) on the third floor, with a score of 0 at 14:47:58
Sp_changedbowner
Change the owner of the current database.

Syntax
Sp_changedbowner [@ loginame =] 'login'
[, [@ Map =] remap_alias_flag]

Parameters
[@ Loginame =] 'login'

The logon ID of the new database owner. The data type of login is sysname, with no default value. Login must be an existing Microsoft & reg; SQL Server logon or Microsoft Windows NT & reg; user. If login has the permission to access the database through an existing alias in the current database or the user's security account, it cannot become the database owner. To avoid this problem, remove the alias or user in the current database.

[@ Map =] remap_alias_flag

The value is true or false, indicating whether the existing alias of the old database owner (dbo) is mapped to the new owner of the current database or needs to be removed. The data type of remap_alias_flag is varchar (5). The default value is NULL, indicating that any existing alias of the old dbo is mapped to the new owner of the current database. False: remove the existing alias of the old database owner.

Return code value
0 (successful) or 1 (failed)

Note
After executing sp_changedbowner, the new owner is called the dbo user in the database. Dbo has the explicit permission to execute all activities in the database.

You cannot change the owner of the master, model, or tempdb system database.

To display a list of valid login values, execute the sp_helplogins stored procedure.

Execute sp_changedbowner with only the login parameter to change the database ownership to login, and map the user alias previously alias dbo to the new database owner.

Permission
Only members of the sysadmin fixed server role or the owner of the current database can execute sp_changedbowner.

Example
The following example makes user Albert the owner of the current database and maps the existing alias of the old database owner to Albert.

EXEC sp_changedbowner 'Albert'

 

Top
Smartcsdn (empty color and dust-free) on the 4th floor replied to-12-15 15:32:46 score 0
If you do not understand, you can read the SQL server books online,

Top
Gmlxf (candlelight) on the fifth floor replied to-12-15 15:45:04, score 0
You can modify objects in batches by using the following methods:

Create procedure dbo. ChangeObjectOwner
@ OldOwner as NVARCHAR (128), -- original parameter owner
@ NewOwner as NVARCHAR (128) -- New Parameter owner
AS

DECLARE @ Name as NVARCHAR (128)
DECLARE @ Owner as NVARCHAR (128)
DECLARE @ OwnerName as NVARCHAR (128)

DECLARE curObject CURSOR
Select 'name' = Name,
'Owner' = user_name (uid)
From sysobjects
Where user_name (uid) = @ OldOwner
Order by name

OPEN curObject
Fetch next from curObject INTO @ Name, @ Owner
WHILE (@ FETCH_STATUS = 0)
BEGIN
If @ Owner = @ OldOwner
Begin
Set @ OwnerName = @ OldOwner + '.' + rtrim (@ Name)
Exec sp_changeobjectowner @ OwnerName, @ NewOwner
End

Fetch next from curObject INTO @ Name, @ Owner
END

Close curObject
Deallocate curObject
GO

Top
Txlicenhe (MA) on the 6th floor replied to-12-15 15:50:50, score 0
As follows:
Exec sp_MSForEachTable 'exec sp_changeobjectowner ''? '', ''Dbo '''

An error may occur. Ignore it. You can change it after you run it.