locked
The database principal owns a service in the database, and cannot be dropped. RRS feed

  • Question

  • Hello All,

    I have a small SQL Server 2008 R2 Database and a User that owns it (default Schema is dbo).

    I have moved this database from another Database server to my Database server. After installing I tried to use it from my Applications but they throw an error that this user has no access into my Database.

    So, I tried to delete that User from my Database so I could create it again, but deleting User didn't succeeded. I'm gettin error "The database principal owns a service in the database, and cannot be dropped."

    I have tried everything that I have founfd from forums to get rid of this error but nothing seems to be working.

    Can anyone please give any practical hints how to remove that User owning this Database or is it possible to attach it to my Database in new Database Server?

    Thanks!

    Wednesday, March 7, 2012 4:35 PM

Answers

  • Since you have restored database SID of User is not mapped with login SID. Instead of dropping, I would suggest to fix the "orphan" user. sp_change_users_login should help.

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    • Marked as answer by milax Wednesday, March 7, 2012 5:08 PM
    Wednesday, March 7, 2012 4:42 PM

All replies

  • Since you have restored database SID of User is not mapped with login SID. Instead of dropping, I would suggest to fix the "orphan" user. sp_change_users_login should help.

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    • Marked as answer by milax Wednesday, March 7, 2012 5:08 PM
    Wednesday, March 7, 2012 4:42 PM
  • Thanks! That helped!
    Wednesday, March 7, 2012 5:08 PM