Not able to Login to the database after restoring on the different Server

indian143

Hi All, I have two database servers, one Dev and another one Test, I made lots of changes on Dev hance I tried to restore the backup of Dev Database on Test Server with same name everything same. Now with the same Credentials Login and Password my web application is failing to access the database on Test, by saying failing to access the Database. Can anybody please help me in getting out of this situation. All I did was, Dev and Test are two different physical servers, took backup of Dev and restored it on the test, now Login fails. Any help a link code snippet or even a suggestion helps a lot. Thanks in advance.

Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

Mycroft Holmes

After restoring a database I run this script to drop and add the expected users. It assumes the users are already created on the new server. The underlying ID for the user name will be different on each server.

Declare @Usr varchar(50),
@SQL as varchar(5000)

Declare csrPV Cursor Fast_Forward For
Select name
From sysusers
Where issqluser = 1
and hasdbaccess = 1
AND name NOT IN ('dbo','IBMSQLDBA')

OPEN csrPV

FETCH NEXT FROM csrPV INTO @Usr

WHILE @@FETCH_STATUS = 0
BEGIN

Set @SQL = 'DROP USER \[' + @Usr + '\]'
Print @SQL
exec (@SQL)

Set @SQL = 'CREATE USER \[' + @Usr + '\] FOR LOGIN \[' + @Usr + '\] WITH DEFAULT\_SCHEMA=\[dbo\]'
print @SQL
exec (@SQL)

Set @sql = 'ALTER LOGIN \[' + @Usr + '\] ENABLE'
exec (@SQL)

EXEC sp\_addrolemember N'db\_owner', @Usr

FETCH NEXT FROM csrPV INTO @Usr

END
CLOSE csrPV
DEALLOCATE csrPV

Never underestimate the power of human stupidity RAH

indian143

Yes it did work thank you very much

Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."