Windows 10: 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. sql server management studio

    in Windows 10 Customization
    sql server management studio: Hi! I have been using an electronic health record system for the past 10 years. I am retiring soon but will still need access to the 'ehr' information for 7 years. The people who run the program that I use tell me that the information is in sql. I have a choice how to access...
  3. sql server install error

    in Windows 10 Software and Apps
    sql server install error: I get the error message of "Version string portion was too short or too long." I've uninstalled all previous versions of SQL and removed any references to .net framework. Windows10-64bit, I5-3GHZ, 16GB ram How do I get past this install error?...
  4. Microsoft SQL Server

    in Windows 10 Installation and Upgrade
    Microsoft SQL Server: I have just had to upgrade from Windows 10 Home to Pro in order to install the update of a Tax program I use. The reason for the upgrade was because the Home edition would not allow the installation of Microsoft SQL Server. So the Pro edition lets it run through to the end...
  5. Microsoft SQL Server

    in Windows 10 Customization
    Microsoft SQL Server: I have just had to upgrade from Windows 10 Home to Pro in order to install the update of a Tax program I use. The reason for the upgrade was because the Home edition would not allow the installation of Microsoft SQL Server. So the Pro edition lets it run through to the end...
  6. Syncing windows 10 Calendar with another user

    in Windows 10 Software and Apps
    Syncing windows 10 Calendar with another user: HI I am trying to set up a shared calendar with my wife using outlook.com. We each have individual calendars on our outlook.com accounts. I have successfully shared my calendar with her as co-owner and this seems to work but only if she posts to my calendar when making...
  7. Computer visible to one user but not another

    in Windows 10 Network and Sharing
    Computer visible to one user but not another: I have 3 computers on my home network, two are Windows 10 desktops, one is Windows 7 laptop. I'll call them 10-1, 10-2, and 7. 10-1 can see 10-2 and 7. 7 can see 10-1 and 10-2. 10-2 can see 7 but CANNOT see 10-1. What can cause this problem? All are Ethernet...
  8. SQL Server error?

    in Windows 10 Network and Sharing
    SQL Server error?: How can I make this SQL Server error go away? [img] 65169
  9. Update SQL server version!

    in Windows 10 Support
    Update SQL server version!: I have 2 computers, a laptop and a desktop, the laptop has SQL 2012 and the Desktop has SQL 2014 with the latest update. I have a program which requires that the SQL versions match, but could not get the laptop to accept the SQL 2014 version. It failed with the message to...
  10. lost SQL Server 2005

    in Windows 10 Support
    lost SQL Server 2005: All: On my Desktop Computer: Downloading pictures from my camera, an error occurred! The error report said that MS SQL Server 2005 was missing! I looked this up & it said SQL server 2005 has been replaced by SQL Server 2015! Is there an easy solution to this...