locked
Cannot drop a user RRS feed

  • Question

  • Dear all,

    I have a login user whihc has been named dbo and attached to a windows login.

    I need to delete that user but SQL do not allows me to delete it whith the following lline :

    IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'dbo')
    DROP USER [dbo]

    Any idea why and how can I remove it ?

    regards

    serge


    Your knowledge is enhanced by that of others.
    Friday, September 10, 2010 8:46 AM

Answers

  • The user dbo is a special one that is required in all databases.  The dbo user is mapped to the database owner login.  Rather than remove the dbo user, you need to change the database owner to a different login using ALTER AUTHORIZATION:

    ALTER AUTHORIZATION ON DATABASE::MyDatabase TO sa; --specify desired database owner login
    

    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Friday, September 10, 2010 12:10 PM

All replies

  • Drop user drops a user from a database, but you said you want to drop a login.  Use drop login instead of drop user, which will remove the login from SQL Server.

    If you are trying to remove the windows user mapped to the dbo user in a database, use drop user [yourdomain\theuser].

    Friday, September 10, 2010 10:48 AM
  • The user dbo is a special one that is required in all databases.  The dbo user is mapped to the database owner login.  Rather than remove the dbo user, you need to change the database owner to a different login using ALTER AUTHORIZATION:

    ALTER AUTHORIZATION ON DATABASE::MyDatabase TO sa; --specify desired database owner login
    

    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Friday, September 10, 2010 12:10 PM
  • Thnaks Dan, that was the clue
    Your knowledge is enhanced by that of others.
    Friday, September 10, 2010 3:41 PM