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.
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 |
|
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. ProcedureThe 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 NortwindCREATE 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 loginsSELECT name, [password], dbname, language, sid, 'skip_encryption', loginnameFROM master..sysloginsORDER BY nameGO-- Remove special SQL logins and all Windows logins.dDELETE loginsWHERE loginname IN ('distributor_admin', 'guest', 'sa')OR loginname LIKE '%\%'GO-- Look at the results.SELECT name, defaultdb FROM loginsCreate.logins.sql-- Create source-server logins on target-serverUSE MasterGoDECLARE logincur CURSORFAST_FORWARDFORSELECT [name], encryptedpassword, defaultdb,deflanguage, sid, encryptoptFROM Northwind..loginsDECLARE @loginame varchar(30),@passwd nvarchar(128),@defdb nvarchar(128),@deflang nvarchar(128),@sid varbinary(85),@encryptopt varchar(30)OPEN logincurFETCH NEXT FROM logincur INTO @loginame, @passwd, @defdb, @deflang, @sid, @encryptoptWHILE (@@fetch_status = 0)BEGINEXEC master..sp_addlogin@loginame, @passwd, @defdb,@deflang, @sid, @encryptoptFETCH NEXT FROM logincur INTO @loginame, @passwd, @defdb, @deflang, @sid, @encryptoptENDCLOSE logincurDEALLOCATE logincurGO |
|
|
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 |
|
|
|
|
|
|
|