Users retained in DB when restoring to alternate server

Question

Post reply

Users retained in DB when restoring to alternate server

Lee-329785

SSC Rookie

Points: 28
More actions
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

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

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

R2ro Default port Points: 1439 More actions · Answer 1

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).

R2ro Default port Points: 1439 More actions · Answer 2

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"

Philip Kelley SSCertifiable Points: 6351 More actions · Answer 3

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

Lee-329785 SSC Rookie Points: 28 More actions · Answer 4

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

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 5

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.

hawg SSCertifiable Points: 6044 More actions · Answer 6

Sorry - ignore this post.

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

----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011