Post reply

Transferring Logins

  • September 10, 2004 at 1:34 am

    #86373

    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

  • September 10, 2004 at 1:53 am

    #522456

    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=',

    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.

     

    LinkedIn Profile

  • September 10, 2004 at 2:13 pm

    #522631

    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

  • September 13, 2004 at 8:33 am

    #522764

    I have had good results using the instructions in this article for transferring logins between Servers.

    http://support.microsoft.com/default.aspx?kbid=246133

  • September 13, 2004 at 8:46 am

    #522770

    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.

  • September 13, 2004 at 8:57 am

    #522772

    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.

     

    LinkedIn Profile

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply