Question regarding restoring a Database

Hello,
I wish to restore a database from another SQL 7 server. The database
restores OK. The original server had a login called "LoginA", which was
enrolled as a user in the database. When I try and add the Login to my
server and enrol it in the restored database it says User already exists.
So, I tried setting us the Login before I restored the database, but the
users are not then enrolled in the restored database. (using enterprise
manager).
I know I can use sp_DropUser to remove it from the restored database and
then re-add it.
However, is there a correct way to restore databases from another Server and
keep the Login / User setting from the original.
Thanks
Tim,Tim
I use this script
Create.LoginsTable.sql
-- Creates table Logins in Northwind
-- containing all SQL Logins on the server
-- (but not 'sa', 'guest', or 'distributor_admin')
USE Northwind
GO
-- Create table Logins in Nortwind
CREATE TABLE [dbo].[Logins] (
[Name] [varchar] (30) NULL ,
[EncryptedPassword] [nvarchar] (128) NULL ,
[DefaultDB] [nvarchar] (128) NULL ,
[DefLanguage] [nvarchar] (128) NULL ,
[sid] [varbinary] (85) NULL ,
[EncryptOpt] [varchar] (30) NULL ,
[LoginName] [varchar] (50) NULL
) ON [PRIMARY]
GO
-- Insert information about the logins into table logins.
INSERT logins
SELECT name, [password], dbname, language, sid,
'skip_encryption', loginname
FROM master..syslogins
ORDER BY name
GO
-- Remove special SQL logins and all Windows logins.
dDELETE logins
WHERE loginname IN ('distributor_admin', 'guest', 'sa')
OR loginname LIKE '%\%'
GO
-- Look at the results.
SELECT name, defaultdb FROM logins
Create.logins.sql
-- Create source-server logins on target-server
USE Master
Go
DECLARE logincur CURSOR
FAST_FORWARD
FOR
SELECT [name], encryptedpassword, defaultdb,
deflanguage, sid, encryptopt
FROM Northwind..logins
DECLARE @.loginame varchar(30),
@.passwd nvarchar(128),
@.defdb nvarchar(128),
@.deflang nvarchar(128),
@.sid varbinary(85),
@.encryptopt varchar(30)
OPEN logincur
FETCH NEXT FROM logincur
INTO @.loginame, @.passwd, @.defdb,
@.deflang, @.sid, @.encryptopt
WHILE (@.@.fetch_status = 0)
BEGIN
EXEC master..sp_addlogin
@.loginame, @.passwd, @.defdb,
@.deflang, @.sid, @.encryptopt
FETCH NEXT FROM logincur
INTO @.loginame, @.passwd, @.defdb,
@.deflang, @.sid, @.encryptopt
END
CLOSE logincur
DEALLOCATE logincur
GO
----
--
--Identify Orphan Users
select u.name from master..syslogins l right join
sysusers u on l.sid = u.sid
where l.sid is null and issqlrole <> 1 and isapprole <> 1
and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'
and u.name <> 'system_function_schema')
"Tim Marsden" <TM@.UK.COM> wrote in message
news:OaMCXDb5DHA.2692@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hello,
> I wish to restore a database from another SQL 7 server. The database
> restores OK. The original server had a login called "LoginA", which was
> enrolled as a user in the database. When I try and add the Login to my
> server and enrol it in the restored database it says User already exists.
> So, I tried setting us the Login before I restored the database, but the
> users are not then enrolled in the restored database. (using enterprise
> manager).
> I know I can use sp_DropUser to remove it from the restored database and
> then re-add it.
> However, is there a correct way to restore databases from another Server

---

and

quote:

---

> keep the Login / User setting from the original.
> Thanks
> Tim,
>

---

|||Many Thanks
I will try it out.
Tim
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23MjE$Qb5DHA.1428@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Tim
> I use this script
> Create.LoginsTable.sql
> -- Creates table Logins in Northwind
> -- containing all SQL Logins on the server
> -- (but not 'sa', 'guest', or 'distributor_admin')
>
> USE Northwind
> GO
> -- Create table Logins in Nortwind
> CREATE TABLE [dbo].[Logins] (
> [Name] [varchar] (30) NULL ,
> [EncryptedPassword] [nvarchar] (128) NULL ,
> [DefaultDB] [nvarchar] (128) NULL ,
> [DefLanguage] [nvarchar] (128) NULL ,
> [sid] [varbinary] (85) NULL ,
> [EncryptOpt] [varchar] (30) NULL ,
> [LoginName] [varchar] (50) NULL
> ) ON [PRIMARY]
> GO
> -- Insert information about the logins into table logins.
> INSERT logins
> SELECT name, [password], dbname, language, sid,
> 'skip_encryption', loginname
> FROM master..syslogins
> ORDER BY name
> GO
> -- Remove special SQL logins and all Windows logins.
> dDELETE logins
> WHERE loginname IN ('distributor_admin', 'guest', 'sa')
> OR loginname LIKE '%\%'
> GO
> -- Look at the results.
> SELECT name, defaultdb FROM logins
>
>
> Create.logins.sql
> -- Create source-server logins on target-server
> USE Master
> Go
> DECLARE logincur CURSOR
> FAST_FORWARD
> FOR
> SELECT [name], encryptedpassword, defaultdb,
> deflanguage, sid, encryptopt
> FROM Northwind..logins
> DECLARE @.loginame varchar(30),
> @.passwd nvarchar(128),
> @.defdb nvarchar(128),
> @.deflang nvarchar(128),
> @.sid varbinary(85),
> @.encryptopt varchar(30)
> OPEN logincur
> FETCH NEXT FROM logincur
> INTO @.loginame, @.passwd, @.defdb,
> @.deflang, @.sid, @.encryptopt
> WHILE (@.@.fetch_status = 0)
> BEGIN
> EXEC master..sp_addlogin
> @.loginame, @.passwd, @.defdb,
> @.deflang, @.sid, @.encryptopt
> FETCH NEXT FROM logincur
> INTO @.loginame, @.passwd, @.defdb,
> @.deflang, @.sid, @.encryptopt
> END
> CLOSE logincur
> DEALLOCATE logincur
> GO
> ----

---

--

quote:

---

> --
> --Identify Orphan Users
> select u.name from master..syslogins l right join
> sysusers u on l.sid = u.sid
> where l.sid is null and issqlrole <> 1 and isapprole <> 1
> and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'
> and u.name <> 'system_function_schema')
>
>
>
> "Tim Marsden" <TM@.UK.COM> wrote in message
> news:OaMCXDb5DHA.2692@.TK2MSFTNGP09.phx.gbl...
exists.[QUOTE]
> and
>

---

|||Hi Tim,
Thank you for using the Newsgroup and it is my pleasure to help you with
you issue.
From my experience ,after restore a user database from one SQL Server to
another, the database user IDs need to be mapped to the existing sql logins
on the new server. You could use sp_change_users_login to link the security
account for a user in the current database to an existing login on the new
host SQL Server without losing the user permission.
Please look for 'sp_change_user_login (T-SQL) " in the SQL Server Books
Online for detailed information.
You could also run the following script to add the old logins into the new
one ( replace testdb with the name of your database)
use testdb --Change to your database name
go
declare @.UserName nvarchar(255)
declare orphanuser_cur cursor for select
UserName = name from sysusers where issqluser = 1 and
(sid is not null and sid <> 0x0) and suser_sname(sid) is null order by name
open orphanuser_cur fetch next from orphanuser_cur into @.UserName while
(@.@.fetch_status = 0)
begin
if not exists (select * from master..sysxlogins where name = @.UserName)
Begin
print @.UserName + ' does not exist in syslogins'
print @.UserName + ' being added to syslogins (reset password and default
database)'
EXEC sp_addlogin @.UserName, 'password', 'testdb' --Change to your
databasename
end
print @.UserName + ' user name being resynced'
EXEC sp_change_users_login 'Update_one', @.UserName, @.UserName
fetch next from orphanuser_cur into @.UserName
end
close orphanuser_cur
deallocate orphanuser_cur
Master database has the logins for the user database and they have been
resynced.
Hope this helps and if you still have questions, please feel free to post
new message here and I am glad to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.