database connectivity problem

Question

database connectivity problem

srirambhatlam

SSC Enthusiast

Points: 167
More actions
October 3, 2008 at 12:00 pm

#382940

Heloo,
I took a back up of 3 databases A,B and C from prod server and restored them on the test server and made sure that they are all up and running.
The problem is that when i'm trying to login to the front end application which connects to database A with a valid user id and password ( i got these parameters from dbo.users table in the database A) it says "can't connect to database". But i'm able to connect to the prod server from front end using same login credentials.
The server login "xyz" is mapped to all databases with a common SID.
Help on this is really appreicated!!!
Thanks
Mahesh

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

CrazyMan SSChampion Points: 12560 More actions · Answer 1

HI Mahesh

Have u checked wether that login you are trying to use exists on your test server. check by logging into your SQL server by using SQL-MS, which version of SQL are you using???

Cheers

🙂

DHeath SSCertifiable Points: 5200 More actions · Answer 2

Hello,

Not sure how your app connects to the database but a lot of mine use an ODBC connection. Is that configured properly if its used.

-D-

DHeath

Miguel Pereira-543757 Mr or Mrs. 500 Points: 524 More actions · Answer 3

Run the following on each DB you restored on your test server:

sp_change_users_login 'report'

Any rows coming back are orphaned users you have on those DBs. If the users coming back are the one you are using to connect your front end application, that explains it.

In all too many instances, you still have to re-link your DB user to your server login after restoring DBs. The user names might be the same on the server, but that UID wil differ, hence why you need to relink them. Obsiously, if you are using an [sa] or [dbo] that won't be the case.

If you need to relink them, and you have the exact same user name and SQL login, you can use this:

sp_change_users_login 'auto_fix','[username]

You should execute that for every user / login you want to relink after a restore operation, for each one of the DBs where it might be necessary.

srirambhatlam SSC Enthusiast Points: 167 More actions · Answer 4

Hey Folks,

Thank you all for the info. I got the problem solved. It was not connecting because after i restored the database, stored procedures where missing.

Thanks

Mahesh