What to do if you cannot delete a user in sql2000/sql2005

SQL Server Delete User steps

1, SQL Server MANAGEMENT studio--database-"security-" framework, first delete the corresponding framework

2, SQL Server MANAGEMENT studio--database-"Security-" users, delete the corresponding user to delete the corresponding schema, and then delete the corresponding users.

The above method I can't delete the user

SQL Server 2000 cannot delete a user's workaround.

This problem was encountered today when data was being transferred, and the solution was as follows:

If you move the SQL database from one server to another, or after you back up the database, uninstall sqlserver2000 and reinstall it, and then restore it, you may encounter this hint about user rights "sql2000 because the selected user owns the object, So can not remove the user ", at this time, delete this user and delete, in the security inside again add it, will prompt this user exists, this how to do?

Due to the MSSQL200 database error, I put an original database to the current SQL, at this time I came to the user, want to restore the user deleted, the result is prompted "because the selected user owns the object, so can not remove the user":

No, I'm going to add the user to this database in the system to see if you can do it, but it prompts for such an error (Microsoft SQL-DMO window): Error 21002:[SQL-DMO] User "Liuhui" already exists

Because of business requirements, I can not re-establish a user to it or change a user, the database can only be used by the user, the standard specification of the server format can not be broken, to not bring the maintenance of the super trouble, no way to help Google and Baidu, the results of the omniscient they let me down, no way, Had to ask an experienced friend to help, the problem was solved.

Workaround:

1. Open Enterprise Manager, expand the server, right click on the local Service, properties:

2. Selecting a general window will automatically open the SQL Server Proxy selection:

3. Repeat action 1. Open the SQL Server Properties (Configuration) window and select the Server Settings window:

will allow the system directory to be directly modified to select, determine

4. Expand the database, see the Database system table sysusers, right--open the table--Return all rows:

5. Select the user who should be added for the restore, locate the related row, and click Delete

In return to the user list, to delete users can be deleted, and then to SQL security inside the database corresponding to the user to add up, testing normal!

Finally return to Action 3 the selection on the hook to allow the system directory directly modify the check to remove

SQL Server 2005 cannot delete a user's workaround.

A database that runs under SQL Server 2005 and cannot be deleted by a database user, prompting the database principal to have a schema in the database and cannot be deleted.

The simple reason is that because this user has ownership of certain schemas in the database, you can remove or remove the schema from the user rights of the related schema.

Take a user UserA as an example to illustrate the specific actions to be taken, and to note the following:

1, database tables, views, stored procedures, and so on, if the schema is UserA, all its owner to dbo.

2, under "Security"-"schema", if there is UserA in the name object, delete it directly, if the owner of the other object is UserA, change its owner to dbo

3. Under Security-Role-database roles, open the properties for each role and remove UserA from the roles list.

After these steps, basically no other problems, can successfully delete invalid database users.

Another way we can use the code to apply the above two versions of the database

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

The code is 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