none
can not drop user from database

    Question

  •  I can not delete user from a database in sql2005 beta 3.
    the message errror is :

    TITLE: SQL Server Management Studio
    ----------------------------------------

    Drop failed for User 'Amministratore'.  (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.0981.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+User&LinkId=20476

    ----------------------------------------
    ADDITIONAL INFORMATION:

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

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

    The database principal owns a schema and cannot be dropped. (Microsoft SQL Server, Error: 15138)

    in sql 2000 I can delete the user very easy, but in sql 2005 I don't understant How to do it.

    Wednesday, October 12, 2005 9:42 AM

Answers

  • You can query the catalogs. For example, you can execute the following query:

    select * from sys.objects where schema_id = schema_id('s')

    to find out the objects that reside in schema 's'.

    Thanks
    Laurentiu

    Thursday, January 05, 2006 1:01 AM
  • In SQL Server 2005, schemas are real entities. You cannot drop a user that owns schemas; you first have to either drop the schemas or change their owner to be another user.

    Thanks
    Laurentiu

    Wednesday, October 12, 2005 5:13 PM
  • Before dropping a schema, it must be empty. Looks like in your case, you still have an object in the schema: Alert_List. You may either choose to drop this object first or you may choose to move it to another schema (using ALTER SCHEMA TRANSFER). When the schema will be empty, you will be able to drop it.

    Thanks
    Laurentiu

    Friday, December 30, 2005 8:39 PM

All replies

  • In SQL Server 2005, schemas are real entities. You cannot drop a user that owns schemas; you first have to either drop the schemas or change their owner to be another user.

    Thanks
    Laurentiu

    Wednesday, October 12, 2005 5:13 PM
  • The previous answer is helpful. I am just elaborating for newbies in SQL 2005.

    1) Expand Schemas(should be like a folder under <yourdatabase> -> Security) .
    2) Delete the unwanted "userSchema".
    3) Then, go back to the User(a folder like thing) and delete it.

    • Proposed as answer by HARIRAM Wednesday, February 29, 2012 6:42 AM
    Thursday, December 01, 2005 4:04 PM
  • Hi,

    I am having the same problem. When i went to delete the schema..i got the error message "drop failed for schema"

    Cannot drop schema 'wch1' because it is being referenced by object 'Alert_List'. (.Net SqlClient Data Provider)

     

    any suggestions???

    Friday, December 30, 2005 7:08 PM
  • Before dropping a schema, it must be empty. Looks like in your case, you still have an object in the schema: Alert_List. You may either choose to drop this object first or you may choose to move it to another schema (using ALTER SCHEMA TRANSFER). When the schema will be empty, you will be able to drop it.

    Thanks
    Laurentiu

    Friday, December 30, 2005 8:39 PM
  • Is there any way i can tell what objects my schema has??
    Tuesday, January 03, 2006 8:40 PM
  • You can query the catalogs. For example, you can execute the following query:

    select * from sys.objects where schema_id = schema_id('s')

    to find out the objects that reside in schema 's'.

    Thanks
    Laurentiu

    Thursday, January 05, 2006 1:01 AM
  • I also had this problem.  I was not able to find out which Schema that the login owned.  I do not know of a stored proc function that will list ownership of schemas given an owner.  Maybe someone can add that to this thread.

     

    I was able to see which schemas were owned by the login by viewing the properties of each schema and seeing who what listed as the owner.

     

    Example:

     

    I opened the properties window of schema db_datareader and notices that the owner was User1.  I changed the owner to be db_datareader and then was able to drop user1.

     

    Regards,

     

    DataSort

    Wednesday, March 21, 2007 6:54 PM
  • Schemas are owned by users, not by logins. In SQL Server, logins and users are not the same thing.

    To find out the schemas owned by a user, you can run the following query:

    select * from sys.schemas where principal_id = user_id('user_name')

    Thanks
    Laurentiu

    Thursday, March 22, 2007 1:16 AM
  • Thank you!
    Thursday, September 06, 2007 4:51 PM
  • I just ran into this problem as well except the error was due to the fact that a User owned two schemas, db_datareader, db_datawriter. 
    I was trying to delete the user from a specific database, and under Security in that database if you open that user and go to its Properties, you can see a green box in the schemas that this User owns.
     
    I then went into the schemas for that database that had the green box checked and saw that the owner was the user specified.
    I changed it back to dbo and was able to delete the user from the database.
     
    Hope this helps.
     
    Tuesday, April 15, 2008 7:13 PM
  • Thanks for this post.  I encountered the same problem.  The user was the owner of db_datareader. 
    Monday, July 21, 2008 8:18 PM
  • Thanks for the information!

     

    Sunday, August 31, 2008 7:52 AM
  • This problem normally occurs when a user owns a schema and you are trying to delete the user.
    There are many workarounds for these and one among the workarounds is

    • In MS SQL Management Studio "Object Explorer" and Expand the [databasename] / Security.
    • Click on Schemas.
    • In summary window, determine which Schema(s) are owned by the user and either change the owner or remove the Scheme(s). [If they are system schema(s), I suggest changine them to 'dbo' or something appropriate.

    The user can then be deleted.

    Click here for original article.
    Thursday, May 07, 2009 5:16 AM
  • Thank you for Posting and Answeruing this Problem.
    It was exactly the solution I needed.

    Thursday, June 25, 2009 7:36 AM
  • I tried to follow this approach, and many others, but the problem is that is not possible to delete also the scheme(s).
    error message:

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Drop failed for Schema 'demo41_en'.  (Microsoft.SqlServer.Smo)

    ------------------------------
    ADDITIONAL INFORMATION:

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

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

    Cannot drop schema 'demo41_en' because it is being referenced by object 'ATTR_INFO'. (Microsoft SQL Server, Error: 3729)




    Tuesday, July 07, 2009 12:55 PM
  • Thanks, I works

     

    1. I found out which is the schema

    2. Drop the schema

    3. Create new schema right User

     

    Thanks All people for your time .

    Note. I happes after restore a backup into a new database overwritten db.

     


    machag01
    Tuesday, November 09, 2010 3:44 PM
  • Hi.

     

    Those fixes didn't work for me. But what did work for me, was to: Right Click the specific User  under the Users Folder. View Properties. Make a note of the names "Owned Schemas" of the "Schemas owned by this user", under General.  Now Look under the  Schemas folder and find the ones that you have just made a note of.

    Right Click each one and change the schema owner to dbo, and Click "OK"

     

    After doing all that, you will now be able to drop the user from the DB.

     

    Enjoy!!

     

     

     

     

    Friday, July 08, 2011 12:52 PM
  • As an alternative to using the SSMS object explorer, you can run a script like the one below to generate the script needed to change the schema owner.  After running the generated script, the user can be dropped (assuming there are no other dependencies on the user).
    SELECT N'ALTER AUTHORIZATION ON SCHEMA::' + QUOTENAME(name) + N' TO dbo;'
    FROM sys.schemas
    WHERE principal_id = USER_ID('UserToDrop');

    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Saturday, July 09, 2011 1:01 PM
  • The previous answer is helpful.

    First delete the schema user then expand the Users Folder (delete Particular user ) now it is successful.

    Example:-

    Database Name = OFFICE

    User Name         = JOHN

    Your Database (Expand OFFICE) --> Security ---> Schemas --> Delete JOHN --> Goto Users -->Now delete JOHN (successfully deleted)

    Wednesday, February 29, 2012 6:58 AM