May 17, 2007 at 11:07 am
Hello.
I am trying to perform a backup and restore of a user's default database. the user- "myuser", was created with a default database. I have backed the user database and tried to restore it in different ways and I always get the same error when trying to connect with the user after restore:
"Cannot open user default database.Login failed (error 4064)".
It happens if I don't delete the user before restoring the database.
If I try to delete the user before restore and create it afterwards, when I try to set the default user database to the same database I get the error:
"user, group or role 'myuser' already exist in the database"
And when I try to delete the user from the restored database (in order to recreate it) I cannot, because the user's schema is connected to objects in the database.
Can anyone help?... How do I restore??
Thanx,
Nili.
May 17, 2007 at 11:48 am
Drop the user from the database.
-- Drop the Schema
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'myuser')
DROP SCHEMA myuser
Re-add the user to the database in
May 19, 2007 at 11:57 am
Thank you for your reply.
I cannot drop the schema, since objects in the database are attached to the schema (and of course I don't want to delete those tables. Whan I try to delete I gat an error message saying that tables are attached to the schema and it cannot be deleted).
May 20, 2007 at 7:38 am
In case anyone has ever came across such a problem here's the solution:
In order for the login who's default database is the restored database to be able to connect to that database again (after the restore), that login needs to be updated with the user attached to the database. This is done using the procedure sp_change_users_login.
After restore, in order to attach an existing login with the restored database user, run this command using sa:
(say the database name is mydb, the user is myuser, and the login name is mylogin):
USE mydb;
GO
EXEC sp_change_users_login 'Update_One', 'myuser', 'mylogin';
GO
Now login mylogin will have the permissions and objects of myuser.
Hope this helps anyone.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply