Logshipping DR-Transfer Logins

  • KKK17 (6/26/2011)


    but my point is to a specific database.

    I dont want to transfer all the logins to the secondary server.

    Something like this would be easier for you, just be sure to substitute YOURDB for the database name you wish to script SQL Server login accounts for. This script will work for SQL Server 2005 onwards

    SELECT 'CREATE LOGIN ' + QUOTENAME(dp.name) + ' WITH PASSWORD = ' +

    master.sys.fn_varbintohexstr(sl.password_hash) + ' HASHED, SID = ' +

    master.sys.fn_varbintohexstr(dp.sid) + ', DEFAULT_DATABASE = ' +

    QUOTENAME(sl.default_database_name) + ', DEFAULT_LANGUAGE = ' + sl.default_language_name +

    ', CHECK_EXPIRATION = ' +

    CASE

    WHEN sl.is_expiration_checked = 0 THEN 'OFF'

    ELSE 'ON'

    END + ', CHECK_POLICY = ' +

    CASE

    WHEN sl.is_policy_checked = 0 THEN 'OFF'

    ELSE 'ON'

    END

    FROM YOURDB.sys.database_principals dp INNER JOIN sys.sql_logins sl

    ON dp.sid = sl.sid

    WHERE dp.type = 'S' AND dp.principal_id > 4

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Hi All,

    Thanks a lot for the replies..

    I have found a script which creates logins on the secondary server using linked server. This script runs on secondary server and can be scheduled to run in a job which looks for any new login and creates the same in secondary with the password.

    But while trying to test it on Test environment, I have found that, only sql logins are getting transferred not the windows login 🙁

    I am not that much good at writing/editing the script as per the requirement..Can anyone help me in editing the attached script. The script needs to create sql as well as windows also on secondary server using open rowset and linked server.

    Please let me know if you have any questions.

    Your help in this regard is greatly appreciated.

    Thanks,

    KKK

  • KKK17 (6/30/2011)


    Hi All,

    Thanks a lot for the replies..

    I have found a script which creates logins on the secondary server using linked server. This script runs on secondary server and can be scheduled to run in a job which looks for any new login and creates the same in secondary with the password.

    But while trying to test it on Test environment, I have found that, only sql logins are getting transferred not the windows login 🙁

    I am not that much good at writing/editing the script as per the requirement..Can anyone help me in editing the attached script. The script needs to create sql as well as windows also on secondary server using open rowset and linked server.

    Please let me know if you have any questions.

    Your help in this regard is greatly appreciated.

    Thanks,

    KKK

    This section of code in the script is pulling SQL Server accounts only

    WHERE

    type = ''''S'''' AND

    You'll need to play around with the login types you want to extract and also modify the script to handle the NULL values for the Windows account passwords. The SIDs for the Windows account are not used in account creation so you'll need to filter that too

    HTH

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Hi,

    I Tested this script which runs on a specific database but it generates the script of only SQL Logins..Not the windows logins..

    Could you please modify this to have both SQL and Windows logins extracted in the output.

    Your help is highly appreciated.

    ~KKK

Viewing 4 posts - 16 through 18 (of 18 total)

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