I am trying to copy an existing Umbraco instance to a new server without success.
I have:
1) Copied all Umbraco files from the root of the existing intance to the new Web server (created a new site, etc.).
2) Restored the SQL Server bak file to the new server.
If I do not create the appropriate login and map it to the restored db, I get the error: "Login failed for user..." (when trying to login to Umbraco or request a page in a browser).
However, when I create the login and try to map it to the db, I get a SQL Server error: "Create failed for User..." and "User, group, or role already exists in the current database."
Details: Umbraco v.4, IIS 7, SQL Server 2008 (the bak file came from a SQL 2005, but that shouldn't be an issue).
If anyone can tell me what I'm doing wrong and how to correctly copy the existing instance, I would greatly appreciate it. Thanks.
What you need to remember is you can add a user at both the server level ( a login ) and the database level ( a user ). So what is happening is that the user probably already exists in your restored DB for that user and you are trying to add the new login but it cannot as the user still exists.
I wrote a SQL script that should reset the user for you, you will need to rename the database to your DB name, the user to the user you want ( currently it is umbracouser ) and of course your password.
/* Script to remove and re-add the umbraco user after a database restore. */ Use YourDatabaseName; DROP USER umbracouser; go DROP LOGIN umbracouser; go CREATE LOGIN umbracouser WITH PASSWORD = 'YOURPASSWORD', DEFAULT_DATABASE = "YourDatabaseName", CHECK_POLICY = OFF; go CREATE USER umbracouser FOR LOGIN [umbracouser] ; GO EXEC sp_addrolemember 'db_datawriter', 'umbracouser'; EXEC sp_addrolemember 'db_datareader', 'umbracouser'; go
Don't forget you need to modify this script with the relevant details for you!
Thanks for your rapid reply! What you said makes perfect sense. In fact, a user with that ID does exist in the newly restored db.
I modified your script for my specific names and ran it but encountered an error:
"The database principal owns a schema in the database, and cannot be dropped."
Any ideas on that?
David
PS Here is the way your script looks with my mods:
/* Script to remove and re-add the umbraco user after a database restore. */ Use SQL2005_430746_aspnetdb; DROP USER SQL2005_430746_aspnetdb_user; go DROP LOGIN SQL2005_430746_aspnetdb_user; go CREATE LOGIN SQL2005_430746_aspnetdb_user WITH PASSWORD = '*******', DEFAULT_DATABASE = "SQL2005_430746_aspnetdb", CHECK_POLICY = OFF; go CREATE USER SQL2005_430746_aspnetdb_user FOR LOGIN [SQL2005_430746_aspnetdb_user] ; GO EXEC sp_addrolemember 'db_datawriter', 'SQL2005_430746_aspnetdb_user'; EXEC sp_addrolemember 'db_datareader', 'SQL2005_430746_aspnetdb_user'; go
Make sure you are not logged into the database as this user ( as obviously we're trying to delete the user )
I suggest the manual approach of opening up your "SQL2005_430746_aspnetdb" database and going to the security section, then manually removing the user front the database. You might need to remove their access to the schema first judging on your error message, it's probably scriptable but for the one off it'll be quicker to do it manually.
The try running the script again, obviously you can ignore errors that say the user does not exists when it's trying to drop the user, ideally my script should be checking to see if the user exists before attempting to drop them, same goes for the login.
Another thought is your user might be marked as the owner of the database, in which case you should mark another user ( like sa ) as the owner and remove the owner flag from the user you are trying to remove.
No problem, please do mark the original solution as helpful (little thumbs) and as the solution :)
Glad I saved the script :) Based on what has happened with your installation I'll try and make it a little bit more robust and post it on my blog shortly.
Your solution was indeed helpful, but I cannot vote yet because
I need at least 70 karma points to be able to rate items, sorry.
However, I did mark your solution with the check mark to indicate "solved".
I hope to gain more points as I recently became an Umbraco Certified
Professional and will be contributing where I can. I've got a few more client
Umbraco projects in the queue and will post any relevant information.
Copy existing Umbraco instance - SQL Server problems
Hi Everyone,
I am trying to copy an existing Umbraco instance to a new server without success.
I have:
1) Copied all Umbraco files from the root of the existing intance to the new Web server (created a new site, etc.).
2) Restored the SQL Server bak file to the new server.
If I do not create the appropriate login and map it to the restored db, I get the error: "Login failed for user..." (when trying to login to Umbraco or request a page in a browser).
However, when I create the login and try to map it to the db, I get a SQL Server error: "Create failed for User..." and "User, group, or role already exists in the current database."
Details: Umbraco v.4, IIS 7, SQL Server 2008 (the bak file came from a SQL 2005, but that shouldn't be an issue).
If anyone can tell me what I'm doing wrong and how to correctly copy the existing instance, I would greatly appreciate it. Thanks.
David Hill
Hi David,
What you need to remember is you can add a user at both the server level ( a login ) and the database level ( a user ). So what is happening is that the user probably already exists in your restored DB for that user and you are trying to add the new login but it cannot as the user still exists.
I wrote a SQL script that should reset the user for you, you will need to rename the database to your DB name, the user to the user you want ( currently it is umbracouser ) and of course your password.
Don't forget you need to modify this script with the relevant details for you!
Cheers,
Chris
Chris,
Thanks for your rapid reply! What you said makes perfect sense. In fact, a user with that ID does exist in the newly restored db.
I modified your script for my specific names and ran it but encountered an error:
"The database principal owns a schema in the database, and cannot be dropped."
Any ideas on that?
David
PS Here is the way your script looks with my mods:
/*
Script to remove and re-add the umbraco user after a database restore.
*/
Use SQL2005_430746_aspnetdb;
DROP USER SQL2005_430746_aspnetdb_user;
go
DROP LOGIN SQL2005_430746_aspnetdb_user;
go
CREATE LOGIN SQL2005_430746_aspnetdb_user WITH PASSWORD = '*******', DEFAULT_DATABASE = "SQL2005_430746_aspnetdb", CHECK_POLICY = OFF;
go
CREATE USER SQL2005_430746_aspnetdb_user FOR LOGIN [SQL2005_430746_aspnetdb_user] ;
GO
EXEC sp_addrolemember 'db_datawriter', 'SQL2005_430746_aspnetdb_user';
EXEC sp_addrolemember 'db_datareader', 'SQL2005_430746_aspnetdb_user';
go
Hi David,
Make sure you are not logged into the database as this user ( as obviously we're trying to delete the user )
I suggest the manual approach of opening up your "SQL2005_430746_aspnetdb" database and going to the security section, then manually removing the user front the database. You might need to remove their access to the schema first judging on your error message, it's probably scriptable but for the one off it'll be quicker to do it manually.
The try running the script again, obviously you can ignore errors that say the user does not exists when it's trying to drop the user, ideally my script should be checking to see if the user exists before attempting to drop them, same goes for the login.
Another thought is your user might be marked as the owner of the database, in which case you should mark another user ( like sa ) as the owner and remove the owner flag from the user you are trying to remove.
Cheers,
Chris
Chris,
Excellent! Deleting the user and schema resolved the issue and I'm able to login to Umbraco and the site works as expected.
Thank you so much for your help.
David
Hi David,
No problem, please do mark the original solution as helpful (little thumbs) and as the solution :)
Glad I saved the script :) Based on what has happened with your installation I'll try and make it a little bit more robust and post it on my blog shortly.
Cheers,
Chris
Chris -
Your solution was indeed helpful, but I cannot vote yet because I need at least 70 karma points to be able to rate items, sorry.
However, I did mark your solution with the check mark to indicate "solved".
I hope to gain more points as I recently became an Umbraco Certified Professional and will be contributing where I can. I've got a few more client Umbraco projects in the queue and will post any relevant information.
Thanks again.
David
is working on a reply...