SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 where are my logins!!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vreede
Starting Member

Netherlands
8 Posts

Posted - 04/09/2002 :  01:46:39  Show Profile  Visit vreede's Homepage  Reply with Quote
Hi,
I have restored a database to a different server, and now I cannot connect to my database with the known users.
The users are known within the database, but not on server level.
However they are in the sysxlogins tabel in the master db.
what can I do??



Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 04/09/2002 :  02:43:20  Show Profile  Visit Merkin's Homepage  Reply with Quote
Hi

You have to recreate the logins, then run this script http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615 and it will re-associate them. It's a pain I know...but it works.

Damian
Go to Top of Page

marileng
Starting Member

Philippines
28 Posts

Posted - 04/09/2002 :  03:14:30  Show Profile  Send marileng a Yahoo! Message  Reply with Quote
I have read an article about Creating Logins for Databases Restored to a Replacement SQL Server and here it is I have tried it also when we replace our server with a new one and it works fine.


A procedure has been devised that copies all the SQL logins (but not ‘sa’, ‘guest’, or other special SQL logins) from the source server to the target server. A test has shown that once the logins are transferred, the database users “show up” in Enterprise Manager, and any of these logins, connecting to the server, can execute procedures or T-SQL commands in the database. Their passwords have been preserved, and the logins’ permissions and roles in each database have been preserved.


Procedure

The logins and their encrypted passwords, default databases and SIDs are copied to a table in the Northwind database. (This database was chosen because it already exists when SQL is installed.) The procedure to do this, Create.LoginsTable.sql, is shown below.


The next step is to DTS the Logins table from <Source>.Northwind to <Target>.Northwind.


In parallel, restore all databases to the target server. This must be completed before creating the logins, since a login will not be created if its default database does not exist.


After all databases are restored, create the logins using the Create.logins.sql procedure, which is shown below.

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




Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 04/09/2002 :  17:23:20  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
You can also use the information in this KB article from Microsoft Number Q246133 to transfer Logins with passwords still encrypted. I used this when transferring from a SQL 7 to a new SQL 2000 server, and it worked very well.

NOTE: Be sure to follow it up with the script Merkin linked to above that realigns Logins to DB Users.

------------------------
GENERAL-ly speaking...

Edited by - AjarnMark on 04/09/2002 17:24:22
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000