How to change the data architecture of mssql2000 to SQLServer2005 database

Found on the Internet: SQL Server 2005 solution to user deletion failure (15138 error) failed
Failed Microsoft SQL Server error: 15138 deletion failed for the user. The database owner has a schema in the database and cannot be deleted.
An error occurred while deleting the *** user. (Microsoft. SqlServer. Smo)

Other information:

An exception occurred when executing a Transact-SQL statement or batch processing. (Microsoft. SqlServer. ConnectionInfo)

Solution 1

Delete the schema of the user, and then delete the corresponding user
Procedure

The code is as follows:	Copy code
1. SQL SERVER MANAGEMENT STUDIO-> database-> Security-> architecture, first delete the corresponding architecture 2. SQL SERVER MANAGEMENT STUDIO-> database-> Security-> User, delete the corresponding user

Solution 2

-Execute the following SQL statement:

Alter authorization on schema: db_owner TO dbo;
-Then manually delete it. Bytes

Here, the architecture cannot be deleted because the architecture of a table is not dbo, so you have to change it to dbo.

In the database testDB, architecture A and User A exist. Now, the user of the database testDB is changed from A to B, and user A is deleted;

The architecture is also changed from A to B, and schema A is deleted as follows:

1. Create user B and then create architecture B;

2. Grant schema A permissions to User B, cancel User A's permissions on schema A, and delete user;

3. Change all database objects belonging to architecture A to architecture B. The code is as follows:

Alter schema [new SCHEMA name] old transfer schema name. [Object table or view or stored procedure in the database]

Alter schema [B] TRANSFER A. [Object 1]

Alter schema [B] TRANSFER A. [object 2]

Alter schema [B] TRANSFER A. [object n] Partition

When you want to change all the tables or stored procedures in a database, the above method can be used, but the speed is too slow, so you have written a stored procedure for batch modification:

Change the stored procedure:

The code is as follows:

Copy code

USE [testDemo] -- -- Alter schema slqwell TRANSFER Create table # t ( ID int IDENTITY () not null, -- creates a column ID and adds 1 to each new record. ProcName varchar (100 ), ) Insert into # t (procName) Select 'dbo. '+ name from sysobjects Where xtype = 'p' and status> = 0 Select * from # t Declare @ I int Declare @ j int Set @ j = 1 Declare @ procName varchar (100) Declare @ SchemaTable varchar (200) While (@ j <(@ I + 1 )) Begin Select @ procName = procName from # t where ID = @ j Set @ j = @ j + 1 Set @ SchemaTable = 'alter SCHEMA sqlwell TRANSFER '+ @ procName Exec (@ SchemaTable) End Drop table # t

The original architecture is dbo, and the first is sqlwell.

To modify the schema of a table, you only need to modify the preceding xtype = 'p' to xtype = 'U' struct.