January 19, 2005 at 11:03 am
I'm not really sure this is the right place to post, but....
This is a SQL issue coming from a develper perspective.
I have a db created on a development machine. I am now at a place where I want to move the db to a production server. The easiest way to do this is to create a back up of the development db, go to the production environment and restore the backup on the production server. (There is no network connection between the two SQL Servers.)
The problem I encounter is that when I restore the backup onto the production server, the .NET app that connects to the db can't establish a connection. I get the following error:
Cannot open databaserequested in login 'name_of_database'. Login fails.
If I create the db and all its objects via scripts and import the data with BCP, there is no issue, only when using a backup to create the db on the production server.
I thought it might be that the User roles created are dropped in the backup and might need to be restored. But, alas, that is not the issue.
Any thots on why the app cannot connect?
dkehler
Five Stones IT Consulting and Development
January 19, 2005 at 11:58 am
Your database user is orphaned. SQL uses security identification numbers (SIDs) to identify users. The userid is provided a SID when it is created on a server. The SID for your user on your development box is different from the SID for the same id on the production box. There are a couple of ways around this. First, you can treat the symptom by using the system stored procedure sp_change_users_login. This will synchronize the SID in the production database with the SID in the master database. Check Books Online for the syntax.
To treat the disease, you can create the login on your production box with the same sid as is on the development box (or vice versa). You can find out what the sid is by querying sysxlogins. Then use the procedure sp_addlogin to add the login to the box that you want to synch. sp_addlogin allows you to specify a sid. Once that is done, you won't have the orphaned logins when you move the database. Of course, if you add a new login, you'll need to add it to the second box this way, or it will become orphaned.
Steve
January 19, 2005 at 12:17 pm
If the login already exists on the production box you can fix the association with the user in the database by running (within the db)
sp_change_users_login 'update_one', 'login', 'login' --where login is the login/user that you want to fix
January 19, 2005 at 12:23 pm
Great! Thank-you both so much.
I should have thought of that, but.... can't know everything I guess.
dkehler
Five Stones IT Consulting and Development
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply