Post reply

Users retained in DB when restoring to alternate server

  • May 18, 2006 at 10:03 am

    #112254

    I have a problem that we were required to restore a database to a different server. Upon doing so the restored database retained the users it used to have even. Attempted to create a user with the same name but couldn't apply the permissions as it said the user already existed. Not to worry I thought I'll remove the user from the database! The next problemt I had is that the user is an owner of three tables so cannot remove. Next step I thought was to run sp_changeobjectowner and set back to sa. Unfortunalty I get the error below. Can someone advise either how to get round this error or a better way of recovering from the issue of the database retaining users from the other server?

    Script run:

    EXEC sp_changeobjectowner 'tablename', 'user'

    ERROR:

    Server: Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 38

    Object 'tablename' does not exist or is not a valid object for this operation.

    Thanks,

    Lee

  • May 18, 2006 at 10:14 am

    #638791

    From BOL (note text in red):

    sp_changeobjectowner

    Changes the owner of an object in the current database.

    Syntax

    sp_changeobjectowner [ @objname = ] 'object' , [ @newowner = ] 'owner'

    Arguments

    [@objname =] 'object'

    Is the name of an existing table, view, or stored procedure in the current database. object is nvarchar(517), with no default. object can be qualified with the existing object owner, in the form existing_owner.object.

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

    Optionally, if the users need to be in the db, sp_change_users_login can be used (also detailed in BOL).

     

  • May 18, 2006 at 10:25 am

    #638797

    BTW, it is a good idea to qualify objects with the owner name and even the db name (e.g. myDB.thatUser.objectA) - less work for the server (trying to find which object) and less work for you when you "DROP TABLE myTable" and you meant to "DROP TABLE theOther.myTable"

  • May 18, 2006 at 4:42 pm

    #638891

    It also sounds like you're having login configuration problems. This is a bit of a complex situation, and this is only a rudimentary explanation; check out BOL for the procedure I reference below for detailed info.

    Assume you have SQL authenticated login MyLogin, and you (manually) configured this on server A and B. Your database was created and supported on A, but now has been moved to B. Using Enterprise Manager, you'll see the login listed as a user in the database, but nothing will work.

    Briefly: when you create a login, it's assigned a unique value (SID) by the server. Two servers = two SIDs; the fact that they have the same name is irrelevant as far as the SQL service is concerned. The database has user MyLogin with SID f(A), but the server only knows login MyLogin with SID f(B); they don't match, so SQL doesn't let you in. (This doesn't happen with NT authentication; the unique identifiers there are managed by the domain, and so will match up in all servers across the domain.)

    System procedure sp_change_users_login can be used to fix this. On the "new" server, run

    EXECUTE sp_change_users_login 'report'

    to identify any problem logins, and run

    EXECUTE sp_change_users_login 'auto_fix', 'MyLogin'

    to synchronize the SIDs. (There's another configuration that lets you delete a problem user. Check out BOL for more info.)

    Do this, and you should then be able to properly manage the database and the objects therein.

    Philip

  • May 19, 2006 at 4:18 am

    #638932

    Thanks Philip,

    Excellent worked a treat , figured there must have be some way of resolving the SIDs.

    I guess this is the only way to resolve when retoring a DB to another server.

    Lee

  • May 19, 2006 at 8:27 am

    #639014

    A better way is to synchronize the SIDs across your servers so backup/restore or detach/attach will work without cleanup.  There are a number of threads on this site that will tell you how, Books Online also covers it.  Briefly, either try the DTS Transfer Logins task, or figure out how to script the correct sp_addlogin commands with a SID parameter.

  • May 19, 2006 at 1:05 pm

    #639082

    Sorry - ignore this post. 

    I just reiterated what was already answered.  I just missed it in my initial read.

     

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

    01010011010100010100110000100000010100110110010101110010011101100110010101110010
    001000000101001001101111011000110110101101110011

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply