Batch modification of MSSQL tables and views and the owner and backup of Stored Procedures

Modify the table owner in batches:
Exec sp_msforeachtable 'exec sp_changeobjectowner ''? '', ''Dbo '''
Modify the table owner:
Exec sp_changeobjectowner 'name of the table to be changed ', 'dbo'

"? ": Name of the original owner. It is equivalent to a wildcard. In addition, the default database owner is DBO. backs up data from a remote server.

The database must consider the owner. Create the corresponding SQL permission user. Otherwise, errors occur frequently. If the default owner is DBO, the SA user in the connection file

And empty passwords are usually valid by default (unless changed ). Otherwise, it is bound to be invalid.

What needs to be remembered is SQL grouping-object-security-Login

Modify views and stored procedures in batches:

Create procedure changeprocowner
@ Oldowner as nvarchar (128 ),
@ Newowner as nvarchar (128)
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 and xtype = 'P'
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

Execute exec changeprocowner 'XX', 'dbo' -- modify the view owner and write it to the original owner name.
Or
Exec changeprocowner '? ', 'Dbo'

Find another method to easily modify the view and stored procedure.

-- 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 owners. 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.

Reference: http://hi.baidu.com/%D1%CE%B3%C7%BB%AF%B9%A4%CD%F8/blog/item/b29ab4d37083e5daa9ec9a9d.html