User login lost when DB restored

[cricketer1]

I have a setup where there is a live production database server and another for development.

The problem is that when I restore data on to my development server from the database backup file of the production database server, I loose all user logins that were created on the development database. So I have to re-create those logins everyday twice, that is how many times the restore is done. Can someone suggest what I need to do so that the logins are not removed on every restore.

Thanks

 

[Liberty]

Why not, when you do your back, create a sql script (once) and every time you restore run the sql script? It should work fine.

 

[Guest_imported]

You can use scripts to remap your users in the new server after you restore. In fact is good to have this kind of t-sql scripts handy since this is a very known issue with db's.
Here is one example, make sure you understand it very well before you apply it to your server! You can copy and past on QA use it at you own risk!
/******************************************************************************
* *
* Procedure: FixLogins.sql *
* Author: Grant Scott *
* Date: 17 March 1999 *
* Description: This script is to be used to reattach users to their *
* correct logins which is especially useful after restoring *
* a DB from one server to another one. It reads through the *
* SysUsers table and retrieves the users/groups of those *
* that do match logins in the SysLogins table within Master. *
* It will then attempt to drop these users from the DB and *
* then add them again. *
* This script will not try to drop users which own objects *
* within the DB nor those users which are attached to *
* logins which own objects. To fix these users 2 scripts *
* should be produced, the first to drop all the non-sa *
* owned objects and a second to recreate them. The relevant *
* data should then be saved through BCPing it or through use *
* of temp tables, the drop script run, this script run, the *
* recreation script run, and the data BCPed or inserted *
* back into the objects. Given time I will change the script *
* to perform these steps as well *
* E-mail: gruntus@ozemail.com.au *
* *
******************************************************************************/


Declare
@suid smallint,
@name char(30),
@grpname char(30),
@sqlstmt char(50)

Create Table #Logins
(name char(30),
grpname char(30))

Declare CurLogins cursor for (select
a.suid, a.name, b.name
from SysUsers a, SysUsers b, Master..SysLogins c
where a.suid <> 1
and a.suid = c.suid
and a.name <> c.name
and a.gid = b.uid
and a.uid not in (select distinct uid
from SysObjects)
and a.name not in (select distinct f.name
from SysUsers d, SysObjects e, Master..SysLogins f
where d.uid = e.uid
and d.suid = f.suid))
for read only

Declare CurAddLogins cursor for (select * from #Logins)

Open CurLogins
Fetch next from CurLogins into
@suid,
@name,
@grpname

While (@@Fetch_status = 0)
begin

select 'Dropping User - ', @name

exec sp_dropuser @name

if @@Error = 0
Insert into #Logins values(@name, @grpname)

Fetch next from CurLogins into
@suid,
@name,
@grpname

end

Close CurLogins
Deallocate CurLogins

Open CurAddLogins
Fetch next from CurAddLogins into
@name,
@grpname

While (@@Fetch_status = 0)
begin

select 'Adding User - ', @name

If exists (select * from Master..SysLogins where name = @name)
exec sp_adduser @name, @name, @grpname

Fetch next from CurAddLogins into
@name,
@grpname

end

Close CurAddLogins
Deallocate CurAddLogins

GO

 

[aalmeida]

If you users are dbo you can try something like this one:You can copy into QA and make sure it does what you need before you apply it to you production servers, use at your own risk.
/************************************************************************/
/***** This batch change the object owner where the uid = UseNAme***/

select * from sysobjects where uid = user_id('heat')
declare @Return int
exec @Return = sp_configure 'allow updates', '1'
SELECT @Return as 'Returned Code'
GO
reconfigure WITH OVERRIDE
GO
DECLARE @Rows int, @Error int
BEGIN TRANSACTION
update sysobjects set uid = user_id('dbo') where uid = user_id('heat')
SELECT @Error = @@Error, @Rows = @@RowCount
SELECT @Rows as '#Rows'
IF @Rows > 0
BEGIN
SELECT @Rows AS '#Rows'
COMMIT TRANSACTION
END
else
BEGIN
SELECT @Error AS 'Error #'
ROLLBACK TRANSACTION
END

exec sp_configure 'allow updates', '0'
reconfigure WITH OVERRIDE
go

 

[cricketer1]

Thank you all for your help. I have tested the scripts on a test setup and they work ...will move to production.