What is the procedures on transfering Logins

  • Hi,

    I have restored my databases restore from one SQL server 2000 to another SQL 2000 Server. Then, I use DTS Transfer Logins untilities to transfer logins. I can see that this DTS runs successfully. In addition, I could check that the all users are transfered. But when I chen these users's permissions, it is lost. So I am wondering should I transfer Logins first by DTS Tools or restore those Databases first, then transfer server Lonins.

    Many thanks.

    Don:)

  • THis is what i did wehn restoring/transferring databases in different server

    1. Run List Log-in and Users (version2000) script

    - (I think I got this in the script section of sqlservercentral)

    - See attachment below (text file)

    2. After running the script you can now detect the server and database user and log-ins.

    3. Create a SQL Script for Server Log-ins and DB User Log-ins

    For Server Log-ins:

    CREATE TABLE login

    (

    names varchar(124),

    pass varchar(124),

    db varchar(124),

    )

    INSERT INTO login VALUES ('server log-in user','password','database')

    DECLARE @login varchar(124)

    DECLARE @q varchar(1024)

    DECLARE @w varchar(1024)

    DECLARE logins CURSOR FOR

    select ''''+names+''''+', '+''''+pass+''''+', '+''''+db+'''' AS 'LOG' FROM login

    OPEN logins

    FETCH NEXT FROM logins INTO @login

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    SET @q = 'sp_addlogin '+@login

    EXEC (@q)

    FETCH NEXT FROM logins INTO @login

    END

    CLOSE logins

    DEALLOCATE logins

    DROP TABLE login

    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

    --Add Role to the Log-in

    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

    DECLARE @login2 varchar(1024)

    CREATE TABLE login2

    (

    names varchar(1024),

    role varchar(3000)

    )

    INSERT INTO login2 VALUES ('server log-in','sysadmin')--there are others like bulkadmin etc.

    DECLARE logins CURSOR FOR

    select names+','+role FROM login2

    OPEN logins

    FETCH NEXT FROM logins INTO @login2

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    SET @w = 'sp_addsrvrolemember '+@login2

    EXEC (@w)

    FETCH NEXT FROM logins INTO @login2

    END

    CLOSE logins

    DEALLOCATE logins

    DROP TABLE login2

    For DB Users:

    --@@@@@lapauser@@@@@--

    DECLARE @lapauser varchar(1024)

    DECLARE @lapauser2 varchar(1024)

    DECLARE @lapauser3 varchar(1024)

    DECLARE lapauser CURSOR for

    select name from master..sysdatabases where name = 'dbname'

    OPEN lapauser

    FETCH NEXT FROM lapauser INTO @lapauser

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    SET @lapauser2 = @lapauser+'..sp_grantdbaccess '+'db user'

    SET @lapauser3 = @lapauser+'..sp_addrolemember '+'db_owner, db user'

    EXEC (@lapauser2)

    EXEC (@lapauser3)

    SET @lapauser3 = @lapauser+'..sp_addrolemember '+'db_accessadmin, db user'

    EXEC (@lapauser3)

    SET @lapauser3 = @lapauser+'..sp_addrolemember '+'db_securityadmin, db user'

    EXEC (@lapauser3)

    SET @lapauser3 = @lapauser+'..sp_addrolemember '+'db_ddladmin, db user'

    EXEC (@lapauser3)

    SET @lapauser3 = @lapauser+'..sp_addrolemember '+'db_backupoperator, db user'

    EXEC (@lapauser3)

    SET @lapauser3 = @lapauser+'..sp_addrolemember '+'db_datareader, db user'

    EXEC (@lapauser3)

    SET @lapauser3 = @lapauser+'..sp_addrolemember '+'db_datawriter, db user'

    EXEC (@lapauser3)

    SET @lapauser3 = @lapauser+'..sp_addrolemember '+'db_denydatareader, db user'

    EXEC (@lapauser3)

    SET @lapauser3 = @lapauser+'..sp_addrolemember '+'db_denydatawriter, db user'

    EXEC (@lapauser3)

    FETCH NEXT FROM apauser INTO @lapauser

    END

    CLOSE lapauser

    DEALLOCATE lapauser

    -I know this process is very long but once done you will just execute the scripts and your server/db log-in/users are already transferred. Any short methods are highly appreciated

    "-=Still Learning=-"

    Lester Policarpio

  • You can also use the Transfer logins task in DTS Designer. It's pretty straight forward to use.

    Markus

    [font="Verdana"]Markus Bohse[/font]

  • I prefer to use the technique described in this Microsoft KB article:

    How to transfer logins and passwords between instances of SQL Server[/url]

    It'll ensure your SIDs match up, meaning you won't have to run sp_change_users_login within the databases. Also, it provides a method to capture and store the logins for use in a disaster recovery scenario without revealing the passwords directly.

    K. Brian Kelley
    @kbriankelley

  • Thanks Markus,

    I tried DTS but it is shallow copy. Now I solve my problem from a stored procedure called sp_help_revlogin from Microsoft website.

    Many Thanks,

    Don

  • I tried DTS first but it is shallow copy, which means it only copies user name but lost the permissions associtaed that user. Now I solve my problem from a stored procedure called sp_help_revlogin from the link you mention before.

    Many Thanks,

    Don

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

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