When a user is removed from MSSQL, the database principal has a workaround in the data inventory that the schema cannot delete _mssql

When you delete a user of a database under Ms Sql2005, prompt " database principal has schema in this database, cannot delete " error solution
1, under the security-> architecture to see if there is no such user exists, if there is to delete
Try again under the user to see if you can delete the user, if not, use the following grammar
Run the following SQL statement
ALTER AUTHORIZATION on SCHEMA::d b_owner to dbo;

--and then manually delete it.
Cannot drop the user because the selected user owns the object "workaround

Copy Code code as follows:

Use your library name.
Go

DECLARE TB cursor Local
For
Select ' Sp_changeobjectowner '
+quotename (
+quotename (USER_NAME (UID))
+'.' +quotename (name), "" "
+ ', ' ' dbo '
From sysobjects
where OBJECTPROPERTY (id,n ' isusertable ') =1
and uid<>user_id (' dbo ')
declare @s nvarchar (4000)
Open TB
Fetch TB into @s
While @ @fetch_status =0
Begin
EXEC (@s)
Fetch TB into @s
End
Close TB
Deallocate TB

Third

Method One (Huaxia Internet professional Technical tip: Because it involves changing database stored procedures, mandatory deletion method is not recommended)
First thing you need to do
Enterprise Manager-〉 Select Server-〉 properties-〉 server settings-〉 pick up to allow system directory ... -〉 OK
Second Step
Find your database to find the sysusers table that your previous user deleted
The third step back, is a * * * value change back
Fourth reconstruction of the user can be

Method Two

Processing for which the selected user owns an object and cannot be deleted for MSSQL

--Execute the following code in Query Analyzer, modify the library name

Copy Code code as follows:

Use your library name.
Go

DECLARE TB cursor Local
For
Select ' Sp_changeobjectowner '
+quotename (
+quotename (USER_NAME (UID))
+'.' +quotename (name), "" "
+ ', ' ' dbo '
From sysobjects
where OBJECTPROPERTY (id,n ' isusertable ') =1
and uid<>user_id (' dbo ')
declare @s nvarchar (4000)
Open TB
Fetch TB into @s
While @ @fetch_status =0
Begin
EXEC (@s)
Fetch TB into @s
End
Close TB
Deallocate TB

Problems that are easily encountered when a MSSQL backup is migrated to another server restore ...
A problem that can be easily encountered when a MSSQL backup is migrated to another server, especially if a database backed up from a virtual host is restored in the native.

The original username and password in SQL will appear invalid
Cannot delete a system table
Invalid prompt table name for select with SA connection
The user name in the original backup database cannot be deleted, prompting that the user cannot be dropped because the selected user owns the object. ”
The main reason is that the original backup restored when the original user's information was retained, resulting in orphaned users ...

At this point, you need to change all of the object's relationships to another user with sp_changeobjectowner, changing the owner of the database object.

Format:

sp_changeobjectowner [@objectname =] ' object ', [@newowner =] ' owner '

Examples are entered in Query Analyzer:

Sp_changeobjectowner ' web102101.tablename ', ' dbo '
In turn, all the owning users are changed to dbo, then the orphaned user is removed from the database user, and the login information is deleted in the security.

And you can create a new user in turn

Here are some additions:

Microsoft SQL Server error: 15138 deletion failed for the user, the database principal has a schema in the database and cannot be deleted. Solving method
Deletion failed for user "* * *". (MICROSOFT.SQLSERVER.SMO)

For help information, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=

9.00.1523.00&evtsrc=microsoft.sqlserver.management.smo.

exceptiontemplates.failedoperationexceptiontext&evtid= Delete +user&linkid=20476

------------------------------
Additional Information:

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

------------------------------

The database principal has a schema in the database and cannot be deleted. (Microsoft SQL Server, error: 15138)

For help information, click: Http://go.microsoft.com/fwlink? prodname=microsoft+sql+server&prodver=09.00.1399&evtsrc=mssqlserver&evtid=15138&linkid=20476



Solution One

First delete the schema for this user, and then delete the corresponding user
Steps
1. SQL SERVER MANAGEMENT studio--Database-"security-" framework, first delete the corresponding architecture
2. SQL SERVER MANAGEMENT studio--Database-security-user, delete the corresponding user

Workaround Two


--Execute the following SQL statement

ALTER AUTHORIZATION on SCHEMA::d b_owner to dbo;
--and then manually delete it.