Transferring Logins
-
Hi.
Can someone please help with the following problem (SQL Server 2000).
I have logins setup on one server. If I backup the database and try to restore the complete database to another server, everything is copied across but the logins get screwed up. I c annot delete the logins. if I click on one the delete it, it appears to go. When I create a new login with the same name, It says it already exists, and when I do a refresh, the one I thought I delete is displayed again.
How can I clear these, and how do I successfully transfer the logins ?
Thanks CCB
-
A login within SQL Server has a unique identifier called a SID and an encrypted password.
The way I do it is to use a SQL query to generate the sp_addlogin command script on the new server.
SELECT 'exec sp_addlogin @loginname='''
+ name
+ ''',@password=',CONVERT(VARBINARY(256), password),
SID ,
'@encryptopt=''skip_encryption'''
FROM Master.dbo.syslogins
WHERE IsNtName=0 AND IsNtGroup=0 AND Name<>'SA'
I run this in QA with the output set to text.
I then copy the results back up into the query pane and run it.
-
AND After you run the above script run
sp_change_users_login (See BOL for info on how to use it)
to Synch the DB with the master just in case you have other logins on that server
HTH
* Noel -
I have had good results using the instructions in this article for transferring logins between Servers.
-
When you backup and restore a user database, the userid's get moved over and not logins. Please make sure that you don't confuse yourself between users and logins. Users are local to the database and logins are on servers.
If you delete a user in the database, the login will not be deleted on the server.
Try using sp_change_users_login procedure as noeld suggested if you want to map userid to logins.
-
The users within a database have a SID column which matches to the SID value of the login.
If you transfer your logins first then restoring your database will maintain your user to login relationship.
The sp_change_users_login procedure is great when autofixing a username that is identical to the login name but otherwise you have to know what the relationship is in the first place.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply