When a user is deleted in MSSQL, the database entity has a schema in the Data Inventory and cannot be deleted.

Source: Internet
Author: User
Tags sql server management sql server management studio

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.