Attach Database problem

• Adam McArdle

  Mr or Mrs. 500

  Points: 540

  More actions

  October 7, 2009 at 8:55 am

  #134285

  I've just attached a database to my 2005 Server. The Attach seems to work fine, however when I try to link my login to the database user I get an error.

  I've had a look at the properties of the login (it's a SQL Server login), under User Mapping, it's mapped to the dbo user and schema on the new datavase. I've tried changing the values in the User and Default Schema columns to be the User and Schema that I want, but I get an error saying "Cannot alter the user 'dbo'"

  I then tried just unchecking the "Map" box, and pressing OK, but it errors saying "Cannot drop the user 'dbo'".

  Why does Management Studio try to drop the dbo user when all I'm trying to do is delete the link between login and user?

  Does anyone know what attaching the database has linked the login to the dbo user?

  And does anyone know how I can get round this issue?

  Thanks in advance.

• oraculum

  SSCrazy

  Points: 2981

  More actions

  October 7, 2009 at 9:13 am

  #1062857

  You are already the 'dbo' of the database so why are you trying to add yourself in again?

  If you wish to change who the dbo is then you need to run sp_changedbowner (i think not used for a while).. and change it to another login.

  Then you can add yourself back in prob as a db_owner!!, if thats what you want..

  Oraculum

• schep021

  Hall of Fame

  Points: 3538

  More actions

  October 7, 2009 at 1:17 pm

  #1063033

  Sometimes the logins become orphaned. Try running the following to see if any logins pop up:

  USE <database_name>;

  GO;

  sp_change_users_login @Action='Report';

  GO;

  If you do have rows returned, here are the instructions on how to fix them:

  http://msdn.microsoft.com/en-us/library/ms175475.aspx