When you delete a database user under ms sql2005, the following message is displayed"The database entity has a schema in the database and cannot be deleted."Error Solution
1. Check whether the user exists under the security-> architecture. If yes, delete the user.
Try again to see if the user can delete the user. If not, use the following syntax.
Run the SQL statement
Alter authorization on schema: db_owner TO dbo;
-- Then manually delete it.
Because the selected user has an object, the user cannot be removed. "solution:
Copy codeThe Code is as follows:
Use your database 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 1)
First, the first thing you need to do
Enterprise Manager> Select Server> Properties> Server Settings> select system directory... -> OK
Step 2
Find your database, find the sysusers table, and delete your previous users.
Step 3 go back. *** the value is changed back.
Part 4: rebuilding users
Method 2
Processing that the selected user has an object in mssql and cannot be deleted
-- Execute the following code in the query analyzer and modify the Database Name
Copy codeThe Code is as follows:
Use your database 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 MSSQL backup is transplanted to another server for restoration ......
Problems that may occur when MSSQL backup is transplanted to another server for restoration, especially when the database backed up from the VM is restored locally...
The original user name and password of the SQL statement are invalid.
A system table cannot be deleted.
When the Select statement is connected using sa, the system prompts that the table name is invalid.
The user name in the original backup database cannot be deleted, and the prompt "the user cannot be deleted because the selected user has an object ."
The main reason is that the original user information is retained during Backup recovery, resulting in isolated users ......
At this time, we need to use sp_changeobjectowner to change all the relationships of the object to another user, both changing the owner of the database object.
Format:
Sp_changeobjectowner [@ objectname =] 'object', [@ newowner =] 'owner'
In the query analyzer example, enter:
Sp_changeobjectowner 'web102101. tablename', 'dbo'
Change all users to dbo in sequence, and delete isolated users from database users, and then delete login information from security.
You can create new users in sequence.
Below are some supplements:
Microsoft SQL Server Error: 15138 failed to delete the database because the database owner has a schema in the database and cannot delete the database. Solution
An error occurred while deleting the *** user. (Microsoft. SqlServer. Smo)
Click: http://go.microsoft.com/fwlink? For help information? ProdName = Microsoft + SQL + Server & ProdVer =
9.00.1523.00 & EvtSrc = Microsoft. SqlServer. Management. Smo.
ExceptionTemplates. FailedOperationExceptionText & EvtID = Delete + User & LinkId = 20476
------------------------------
Other information:
An exception occurred when executing a Transact-SQL statement or batch processing. (Microsoft. SqlServer. ConnectionInfo)
------------------------------
The database entity has a schema in the database and cannot be deleted. (Microsoft SQL Server, error: 15138)
Click: http://go.microsoft.com/fwlink? For help information? ProdName = Microsoft + SQL + Server & ProdVer = 09.00.1399 & EvtSrc = MSSQLServer & EvtID = 15138 & LinkId = 20476
Solution 1
Delete the schema of the user, and then delete the corresponding user
Procedure
1. SQL SERVER MANAGEMENT STUDIO -- database -- Security -- Architecture, first Delete the corresponding Architecture
2. SQL SERVER MANAGEMENT STUDIO -- database -- Security -- user, delete corresponding user
Solution 2
-- Execute the following SQL statement
Alter authorization on schema: db_owner TO dbo;
-- Then manually delete it.