Problem with moving databases

[seiamerica]

I had to move the database from production server to developement server. So I did backup of the database in the production server and moved the file to dev and did a restore on the database in the dev server. Everything went fine. but when I tried to access the site on the dev server I get a popup for username and password. There is a user created for that particular database which the site is accessing. When I tried to create I get the adduser already exists. I am struggling with this issue since yesterday. Any help is greatly appreciated.

 

[SQLDenis]

delete the use from the DB, then go to securirty/logins and give the user permissions to that DB again (if the user exists)
If the user doesn't exist in securirty/logins you have to recreate the user and give DB permissions

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[timscronin]

Is this using sql or Nt/SQL logins, there is a DTS job to move logins from server to server which you should look at.

 

[mrdenny]

Use the sp_change_users_login to realign the users with the logins.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

I don't know if this will help or not, but here goes:

We've actually had problems with restoring our production database to Dev or QC (the reverse of this issue) and the logins having multiple records in the system databases / tables.

To resolve this issue, we run the following script to fix all the users.

DECLARE @username varchar(25)
	DECLARE fixusers CURSOR
	FOR 
	SELECT UserName = users.name FROM sysusers users
	JOIN master.dbo.syslogins logins
 	ON users.name=logins.name
	WHERE users.uid > 2 AND users.issqluser = 1
	ORDER BY users.name

	OPEN fixusers
	FETCH NEXT FROM fixusers
	INTO @username
	WHILE @@FETCH_STATUS = 0

	BEGIN
		EXEC sp_change_users_login 'update_one', @username, @username
		FETCH NEXT FROM fixusers
		INTO @username
	END

	CLOSE fixusers
	DEALLOCATE fixusers

Of course, if you just moved the .mdf & .ldf files, this probably won't help. If you restored the database, though, it might.



Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.