locked
Unable to delete user RRS feed

  • Question

  • When trying to delete a user on SQL server 2000, we recieve the message "You cannot drop the selected login ID because that login ID owns objects in one or more databases."

     

    We've tried a number of operations to find the objects owned by the user by searching for this error message in Google but, none of those suggestions work.

     

    My questions are;  Why does this happen, How can we avoid it in the future?

     

    Our software applicaiton is case sensistive, and if a SQL security login is not created that matches the Domain login to the method used in the application - somehow we are suck.  We can't create a new security for the login, nor can we delete or use the existing one.

     

     

    Monday, August 27, 2007 4:18 PM

Answers

  • Its coz in sql server 2000 the User(or Login) and objects are tightly coupled. Objects are owned by user. before droping user u have to transfer the ownership of object to someother user and then drop. But in 2005 , schema is the owner of the object and so the problem is not there in 2005.

    by querying syspermissions, sysprotects, sysobjects, sysusers, syscolumns you can find the objects owned by  a user

    refer http://www.databasejournal.com/features/mssql/article.php/3379521


    After finding the objects change the owner of the object to someother user using sp_changeobjectowner

     

    Madhu

    Monday, August 27, 2007 5:25 PM