April 15, 2012 at 9:33 am
I'm making a copy of an web application for testing. To make this work, I need a copy of the database. I successfully restored the database using Task / Restore / Database where I changed the To database to a new name.
In the configuration for my web application I changed the connection string to reference the new database.
Then I took the original database Offline.
When I run the application, it complains that it can't open the original database because it's offline.
The database has a user (webuser) that enables it to run stored procedures. When I delete this user from the new database and run the application, an error is returned that the new database can't be opened because the login failed for webuser.
Clearly the application points to the new database and yet it's still connected to the original database.
What do I need to do to make my new database available?
April 15, 2012 at 12:24 pm
Its might be like in your code old database name is used that's why your application is connecting the old one only. so find out the issue in your application.
April 15, 2012 at 1:05 pm
Thanks for your reply. I'm sure the application is pointing to the new database because of the error message when I delete the user. The message tells me that the new database can't be opened because it can't log into the (deleted) user.
Additionally, I have a restored copy of the production database that I can access in the same manner - by simply changing the connection string in the configuration of the web application. This should be no different.
April 15, 2012 at 1:35 pm
in the properties of the database i think you should change the owner of database to "sa" in the files
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 15, 2012 at 1:45 pm
When a database is restored it is owned by the user performing the restore. Since I was running SSMS as sa when I perform the restore, the new database owner is sa.
My guess is that the database users still point to the original database. I have schemas and roles tied to the users so I don't want to delete and readded them. If this idea has merit, how would I force the users to reference the new database?
April 15, 2012 at 1:55 pm
how many instance do you have in your server?
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 15, 2012 at 2:00 pm
On this server I have one instance of SQL Server running and including the new database, three databases.
April 15, 2012 at 2:24 pm
You moved a copy of the database, what did you do about the logins that access the database did you transfer these too?
Open a new query window against the newly restored database and run the following
exec sp_change_users_login 'report'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 15, 2012 at 3:32 pm
Then I took the original database Offline.
When I run the application, it complains that it can't open the original database because it's offline.
you still had this error...........
what is the logins default database?
---------------------------------------------------------------------
April 15, 2012 at 3:35 pm
also look for any login failure error messages in the sql errorlog and post the state no here.
---------------------------------------------------------------------
April 15, 2012 at 3:40 pm
if you change the connection string to another database do you have this error again ?
i think you should change the connection string to another database and check the application again .
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 15, 2012 at 4:20 pm
Open a new query window against the newly restored database and run the following
exec sp_change_users_login 'report'
Perry, I ran this and it returned nothing. I see this is part of an auto_fix for logins. I ran this too but it didn't change anything.
Because he new database is on the same sever as the original, I didn't have to change the server logins.
George, the default databases for the server logins are master.
From the errorlog:
2012-04-15 14:57:52.04 spid52 Setting database option OFFLINE to ON for database original database.
2012-04-15 14:57:57.96 Logon Error: 18456, Severity: 14, State: 38.
2012-04-15 14:57:57.96 Logon Login failed for user 'Server\webuser'. Reason: Failed to open the explicitly specified database.
April 15, 2012 at 4:36 pm
Perry, I ran
exec sp_change_users_login 'report'
but it returned nothing.
George, the server logins all default to master. I did this to ensure the application didn't get steered in the wrong direction.
From the errorlog:
2012-04-15 14:57:52.04 spid52 Setting database option OFFLINE to ON for database originalDB.
2012-04-15 14:57:57.96 Logon Error: 18456, Severity: 14, State: 38.
2012-04-15 14:57:57.96 Logon Login failed for user 'SERVER\webuser'. Reason: Failed to open the explicitly specified database.
April 15, 2012 at 4:44 pm
Larry, I can change the connection string to point to a restored copy of the production database and it works fine. Two things are different. This database came from a different server and I didn't have to change the database name when I restored it. Having said that, the database user (webuser) is restored with reference to the production server so I had to create a new user that is local to the test server.
April 15, 2012 at 11:51 pm
jim.hamer (4/15/2012)
so I had to create a new user that is local to the test server.
You said you restored the database back to the same server as the original and didn't have to re create any logins???
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply