SQL Server 2000 - dump, copy and restore

[hsandwick]

We were receiving login errors with the infamous 28000 code when our users attempted to run certain Crystal Reports containing db views, and reading up about this I learned that when we do a backup of the db, or move the db to another server, or similar, a copy job will only transfer all tables and views, and not the logins. Our login role set up for users, therefore, was missing. I've created a "login transfer" local package to run after the copy and restore, and this seems to take care of the issue.

Is there anything else I need to set up to run, to ensure we are copying over everything necessary, please?

Thanks,

Helen

 

[mutley1]

Hi Helen,

When you copy it to A N Other server, is there anything else on the server you are moving the database to? If not, then I would restore master, msdb and the live db. That way, your logins are preserverd in the master, and jobs you may have running on the DB during the day will be there (msdb) and the DB....of course!

Rgds,

M.

 

[hsandwick]

Hello, Mutley1,

Sorry for such a delay in responding. Turns out, while the master logins copy over with the same IDs, the logins at the sub-database level have unique ids in each environment. Therefore, when logins are copied over, they over-write the unique id at the sub-database level. So, we must not copy over the logins at this level, but instead must ensure the ids match in both environments. This seems to do the trick.

Thanks,

Helen

 

[Catadmin]

Helen,

If you don't already have this code, copy it into a text file for future reference. It'll sync all your logins for the copied DB against the logins that are already on your SQL Server.

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

Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"