locked
Transfer logins RRS feed

  • Question

  • Hello.  I need some help automating transfer of logins from principal server to mirror server (SQL Server 2008 R2) using a SQL Agent job.  I want to use sp_help_revlogin to dump a list of logins on the principal server to a .sql text file.  Then I want to put the text file on the mirror server, so in the event of a failover (which we will do manually), we can use the .sql file to restore the current logins on the mirror.  It looks like an effective way to dump the logins to a text file on the principal is by using SQLCMD:

      sqlcmd -E -d master -Q "exec sp_help_revlogin" -o "C\temp\logins.sql"

    Is there a way to use variables with SQLCMD to dump the text file directly to the mirror instead of the principal?  I can query sys.database_mirroring to get the name of the mirror server, but I'm I'm stuck on how to stuff the mirror server name into a variable so that SQLCMD evaluates to:

      sqlcmd -E -d master -Q "exec sp_help_revlogin" -o <a href="file://\\\temp\logins.sql">\\<mirror_server>\temp\logins.sql

    I guess I could just create a separate job step to copy the file from principal to mirror instead of trying to use variables, but I'd like to try variables first.

    Friday, May 24, 2013 5:28 PM

All replies

  • The SQLCMD in my original post should be:

    sqlcmd -E -d master -Q "exec sp_help_revlogin" -o "\\mirror_server_name\temp\logins.sql"

    Friday, May 24, 2013 5:30 PM
  • --SQL Logins:
    SELECT 'CREATE LOGIN ' + QUOTENAME(name) + ' WITH PASSWORD='
    + sys.fn_varbintohexstr(password_hash) + ' HASHED, SID='
    + sys.fn_varbintohexstr(sid) + ', '
    + 'DEFAULT_DATABASE='+ QUOTENAME(COALESCE(default_database_name, 'master'))
    + ', DEFAULT_LANGUAGE=' + QUOTENAME(COALESCE(default_language_name, 
    'us_english'))
    + ', CHECK_EXPIRATION=' + CASE is_expiration_checked WHEN 1 THEN 'ON' ELSE 
    'OFF' END
    + ', CHECK_POLICY=' + CASE is_policy_checked WHEN 1 THEN 'ON' ELSE 'OFF' END
    FROM sys.sql_logins
    WHERE name<>'sa'
    UNION ALL
    --Windows logins:
    SELECT 'CREATE LOGIN ' + QUOTENAME(name) + ' FROM WINDOWS WITH '
    + 'DEFAULT_DATABASE='+ QUOTENAME(COALESCE(default_database_name, 'master'))
    + ', DEFAULT_LANGUAGE=' + QUOTENAME(COALESCE(default_language_name, 
    'us_english'))
    FROM sys.server_principals
    WHERE type IN ('U','G')
    AND name NOT LIKE '%\SQLServer2005MSSQLUser$%$%'
    AND name NOT LIKE '%\SQLServer2005SQLAgentUser$%$%'
    AND name NOT LIKE '%\SQLServer2005MSFTEUser$%$%'
    AND name NOT IN ('BUILTIN\Administrators', 'NT AUTHORITY\SYSTEM');

    --Problem: when executing CREATE LOGINs generated by the query above,
    --it is not possible to assign default database when that database is 
    mirrored or unavailable for some other reason.
    --(filled a bug for that, this needs to be solved)

    --Server roles for logins:
    SELECT 'EXEC sp_addsrvrolemember ' + QUOTENAME(L.name) + ', ' + 
    QUOTENAME(R.name)
    FROM sys.server_principals L JOIN sys.server_role_members RM
    ON L.principal_id=RM.member_principal_id
    JOIN sys.server_principals R
    ON RM.role_principal_id=R.principal_id
    WHERE L.type IN ('U','G','S')
    AND L.name NOT LIKE '%\SQLServer2005MSSQLUser$%$%'
    AND L.name NOT LIKE '%\SQLServer2005SQLAgentUser$%$%'
    AND L.name NOT LIKE '%\SQLServer2005MSFTEUser$%$%'
    AND L.name NOT IN ('BUILTIN\Administrators', 'NT AUTHORITY\SYSTEM', 'sa');

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Proposed as answer by Mike Yin Sunday, May 26, 2013 4:30 PM
    Sunday, May 26, 2013 7:05 AM