Friday, March 9, 2012

Error 4064 after attempting restore backup?!

Hi,

We have a little problem for you:

We have 2 servers both running sqlexpress2005, both have service pack 1 installed, both have the same db (same tables etc.) both have the same users with the same passwords. Everything is identical.

What I want to do is move the db. I am attempting to do this by making a backup of the db and restoring it into the second machine. (I am not sure of a more practical way to do this without stopping the SQL server).

This is the problem, the backup process works fine, without error, but when restoring the copy to the other server ,it loses the dbowner property and all default users when attempting to connect recieve a 4064 error even though all the password for users are there. Very strange.

I tried to restore the copy to to the same server that I made the copy and everything works, this only happens when attempting to restore the copy on another machine.

Do you know if this is a possible bug? I have tried to do further test on 4 different machines now and the same happens.

The objective is to be able to send the data from sqlexpress server to another by making a backup and without the need t stop the sql server. Any suggestion would be appreciated.

Adam

Even though the usernames and passwords are the same the users will have different SIDs (SQL Server's unique identifiers for users). To get a list of the orphaned users run the following on the database you restored:

EXEC sp_change_users_login 'Report'

You can then run the following command for each orphaned user to fix their relationship to the database:

EXEC sp_change_users_login 'Auto_Fix', 'user'

|||

William, can you help me with this:

I have the same scenario as the original posting, and the orpahned user (Webuser) can be fixed with the following:

1 - EXEC sp_change_users_login 'Auto_Fix', 'webuser'

2- I must then create a table, and give webuser rights to it.

Until I do step 2 the user webuser still cannot select from any tables

How can I simplify this and allow webuser to use the db without the second step.

thanks for any help

regards

David

|||

hi David,

step 1 only fixex membership to the database users... being a database user only grants (again) membership to "public" database role, and usually public members do not have lots of permissions on database objects... acutally public members do not have read/write permissions on database tables at all.. you have to manually handle that, or make the desired/required database user member of other privileged database roles, like db_datareader/db_datawriter.. or, even better, define appropriate database roles to have the desired granularity in object's permissions...

regards

|||Hi
Sorry I did not explain adequately.

My user already has rights to all necessary tables and other objects in the db.

I am having to update a remote server with the latest version if a db, I have

no online link to that server the only possible method is manually

overwriting the mdb and ldb files

After I re-attach the updated file to the server, the user cannot select from any of those objects that they formerly had access to.

I run the sp to deal with the orphaned users issue, but then, the user still cannot select etc, even though I can look into all the object permissions and the user is shown there. So they are not an orphan, and they have explicit permissions, but they cannot see or use any of the database.

The only way I have found to resolve it is to create a new object, a table say, grant the user permissions, then hey presto suddenly they can see that, and more to the point they can see and use all the objects they are supposed to see. Then I delete the newly created table object.

The act of creating a new table and giving them rights has fixed the problem. But it cannot be the right way to do it.

regards
David

No comments:

Post a Comment