where are my logins!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

where are my logins!!

Author

Topic

vreede
Starting Member

8 Posts

Posted - 2002-04-09 : 01:46:39

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!

4970 Posts

Posted - 2002-04-09 : 02:43:20

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

marileng
Starting Member

28 Posts

Posted - 2002-04-09 : 03:14:30

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

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-04-09 : 17:23:20

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources