How to: Sync SQL users from one server to another

Discus and support How to: Sync SQL users from one server to another in Windows 10 Tutorials to solve the problem; When you have SQL Clustering with Always On, the databases are sycnhronised and the failover works like a charm. The only problem is that the logins... Discussion in 'Windows 10 Tutorials' started by Noel, Jun 21, 2018.

  1. Noel New Member

    How to: Sync SQL users from one server to another


    When you have SQL Clustering with Always On, the databases are sycnhronised and the failover works like a charm. The only problem is that the logins are not synchronized from one server to another. To export the current users and import to another server, on the main server execute the below T-SQL.

    USE master
    GO
    IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
    DROP PROCEDURE sp_hexadecimal
    GO
    CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
    AS
    DECLARE @charvalue varchar (514)
    DECLARE @i int
    DECLARE @length int
    DECLARE @hexstring char(16)
    SELECT @charvalue = '0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH (@binvalue)
    SELECT @hexstring = '0123456789ABCDEF'
    WHILE (@i 'sa'
    ELSE
    DECLARE login_curs CURSOR FOR

    SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
    sys.server_principals p LEFT JOIN sys.syslogins l
    ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
    OPEN login_curs

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
    IF (@@fetch_status = -1)
    BEGIN
    PRINT 'No login(s) found.'
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN -1
    END
    SET @tmpstr = '/* sp_help_revlogin script '
    PRINT @tmpstr
    SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
    PRINT @tmpstr
    PRINT ''
    WHILE (@@fetch_status <> -1)
    BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

    SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
    -- obtain password and sid
    SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
    EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

    -- obtain password policy state
    SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
    SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

    SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

    IF ( @is_policy_checked IS NOT NULL )
    BEGIN
    SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
    END
    IF ( @is_expiration_checked IS NOT NULL )
    BEGIN
    SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
    END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
    SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
    SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
    SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
    END

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
    END
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN 0
    GO

    Once this is done, run the below T-SQL on the main SQL server

    EXEC sp_help_revlogin

    This will output the script to be used on the replica server. Copy the output and paste it on the connection to the replica server. After executing you will find the users with the same password from the main server.

    (18)

    read more...
     
  2. Brink Win User

    SQL Server + Python — What’s new


    Source: SQL Server + Python s new | SQL Server Blog
     
    Brink, Jun 21, 2018
    #2
  3. Dyan Rey Win User
    SQL server 2014

    Hello,

    Incorrect user permissions is one of the possible reasons why you're having challenges in downloading SQL server 2014. We suggest that you check this
    link. This page contains information on how to obtain the latest service pack for SQL
    Server 2014.

    Let us know how else we may help you.  
     
    Dyan Rey, Jun 21, 2018
    #3
  4. Brink Win User

    How to: Sync SQL users from one server to another

    SQL Server + PHP – What’s new


    Source: SQL Server + PHP s new | SQL Server Blog
     
    Brink, Jun 21, 2018
    #4
Thema:

How to: Sync SQL users from one server to another

Loading...
  1. How to: Sync SQL users from one server to another - Similar Threads - Sync SQL users

  2. Windows 10 calendar won't sync with Icloud calendar.

    in Windows 10 Software and Apps
    Windows 10 calendar won't sync with Icloud calendar.: I have Windows 10 but the calendar will not sync with my iphone calendar. It keeps saying fix account. But the password is correct. Still will not sync. I have the People App, but it's useless and will not sync my contacts with my iphone either. Please Help?? Moved from:...
  3. Syncing settings on Windows 10

    in Windows 10 Customization
    Syncing settings on Windows 10: Okay, so from what I understand, I can sync my personalization settings to all of my Windows 10 devices that I use with my Microsoft account. I have my own personal laptop PC, and a family shared computer where we all have our own individual profiles and desktops... but my...
  4. Onedrive album sync

    in Windows 10 Software and Apps
    Onedrive album sync: Hi, I have created some new albums in Microsoft photo app. My onedrive is synced with photo app. But the albums that I have created is not getting synced with onedrive automatically. I have to click on share and it takes time to sync. is it recopy the images? How to...
  5. Sync same folder with OneDrive and Google Drive

    in Windows 10 Network and Sharing
    Sync same folder with OneDrive and Google Drive: Can a same folder be Sync with OneDrive and Google Drive. Any harm. 114123
  6. Sync background on extended view

    in Windows 10 Support
    Sync background on extended view: I have a surface laptop with a Dell monitor and windows 10. I am using the monitors in extended option as it good to work with. How can I have the same background slide show display on both my laptop and monitor as they are now out of sync? Cheers Rinus 114110
  7. RDP syncing?

    in AntiVirus, Firewalls and System Security
    RDP syncing?: I connect to one terminal server running Windows Server 2012 R2 from two Windows 10 machines. (I connect from Macs, too, but they don't share this issue.) I connect to the server with Microsoft's RDP client that is native to Windows 10. Both Windows 10 machines have local...
  8. Windows 10 Mail app using high CPU to sync Google

    in Windows 10 Software and Apps
    Windows 10 Mail app using high CPU to sync Google: This started a few months ago and I put on feedback but haven't heard anything. At any rate all of a sudden Windows 10 Mail is constantly using 30%+ CPU on my i7 Surface Book. It's killing the battery life and the fan annoying. Others appear to be having the same issue...
  9. Error 0x80070057 when syncing my email on the Windows 10 application

    in Windows 10 Customization
    Error 0x80070057 when syncing my email on the Windows 10 application: When i am trying to sync my emails on the windows 10 application, i get an error 0x80070057, and it directs me to answers.microsoft.com. Can you please tell me what steps to take to fix this problems?...
  10. OneDrive sync false error message

    in Windows 10 Customization
    OneDrive sync false error message: I resinstalled my Windwos 10 pro, on my computer. In previous installation OneDrive has offline storage on this computer with some folder excepted form sync. After I setup OneDrive option to sync. the offline storage with online version, and set excepted folders from sync....