{"id":264,"date":"2016-06-24T16:14:17","date_gmt":"2016-06-24T14:14:17","guid":{"rendered":"http:\/\/blog.georgebaciu.pro\/?p=264"},"modified":"2016-09-07T15:20:43","modified_gmt":"2016-09-07T13:20:43","slug":"how-to-move-users-between-sql-servers","status":"publish","type":"post","link":"https:\/\/blog.georgebaciu.pro\/?p=264","title":{"rendered":"How to move users between SQL Servers"},"content":{"rendered":"<p>MSDN is the best documentation in the world &#8211; read <a href=\"https:\/\/support.microsoft.com\/en-us\/kb\/918992\" target=\"_blank\">the original post here<\/a>.<\/p>\n<ol>\n<li>Create\u00a0 helper stored procedures<\/li>\n<\/ol>\n<pre><code>USE master\r\nGO\r\nIF OBJECT_ID ('sp_hexadecimal') IS NOT NULL\r\n  DROP PROCEDURE sp_hexadecimal\r\nGO\r\nCREATE PROCEDURE sp_hexadecimal\r\n    @binvalue varbinary(256),\r\n    @hexvalue varchar (514) OUTPUT\r\nAS\r\nDECLARE @charvalue varchar (514)\r\nDECLARE @i int\r\nDECLARE @length int\r\nDECLARE @hexstring char(16)\r\nSELECT @charvalue = '0x'\r\nSELECT @i = 1\r\nSELECT @length = DATALENGTH (@binvalue)\r\nSELECT @hexstring = '0123456789ABCDEF'\r\nWHILE (@i &lt;= @length)\r\nBEGIN\r\n  DECLARE @tempint int\r\n  DECLARE @firstint int\r\n  DECLARE @secondint int\r\n  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))\r\n  SELECT @firstint = FLOOR(@tempint\/16)\r\n  SELECT @secondint = @tempint - (@firstint*16)\r\n  SELECT @charvalue = @charvalue +\r\n    SUBSTRING(@hexstring, @firstint+1, 1) +\r\n    SUBSTRING(@hexstring, @secondint+1, 1)\r\n  SELECT @i = @i + 1\r\nEND\r\n\r\nSELECT @hexvalue = @charvalue\r\nGO\r\n \r\nIF OBJECT_ID ('sp_help_revlogin') IS NOT NULL\r\n  DROP PROCEDURE sp_help_revlogin\r\nGO\r\nCREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS\r\nDECLARE @name sysname\r\nDECLARE @type varchar (1)\r\nDECLARE @hasaccess int\r\nDECLARE @denylogin int\r\nDECLARE @is_disabled int\r\nDECLARE @PWD_varbinary  varbinary (256)\r\nDECLARE @PWD_string  varchar (514)\r\nDECLARE @SID_varbinary varbinary (85)\r\nDECLARE @SID_string varchar (514)\r\nDECLARE @tmpstr  varchar (1024)\r\nDECLARE @is_policy_checked varchar (3)\r\nDECLARE @is_expiration_checked varchar (3)\r\n\r\nDECLARE @defaultdb sysname\r\n \r\nIF (@login_name IS NULL)\r\n  DECLARE login_curs CURSOR FOR\r\n\r\n      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM \r\nsys.server_principals p LEFT JOIN sys.syslogins l\r\n      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name &lt;&gt; 'sa'\r\nELSE\r\n  DECLARE login_curs CURSOR FOR\r\n\r\n\r\n      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM \r\nsys.server_principals p LEFT JOIN sys.syslogins l\r\n      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name\r\nOPEN login_curs\r\n\r\nFETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin\r\nIF (@@fetch_status = -1)\r\nBEGIN\r\n  PRINT 'No login(s) found.'\r\n  CLOSE login_curs\r\n  DEALLOCATE login_curs\r\n  RETURN -1\r\nEND\r\nSET @tmpstr = '\/* sp_help_revlogin script '\r\nPRINT @tmpstr\r\nSET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' *\/'\r\nPRINT @tmpstr\r\nPRINT ''\r\nWHILE (@@fetch_status &lt;&gt; -1)\r\nBEGIN\r\n  IF (@@fetch_status &lt;&gt; -2)\r\n  BEGIN\r\n    PRINT ''\r\n    SET @tmpstr = '-- Login: ' + @name\r\n    PRINT @tmpstr\r\n    IF (@type IN ( 'G', 'U'))\r\n    BEGIN -- NT authenticated account\/group\r\n\r\n      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'\r\n    END\r\n    ELSE BEGIN -- SQL Server authentication\r\n        -- obtain password and sid\r\n            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )\r\n        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT\r\n        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT\r\n \r\n        -- obtain password policy state\r\n        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\r\n        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\r\n \r\n            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'\r\n\r\n        IF ( @is_policy_checked IS NOT NULL )\r\n        BEGIN\r\n          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked\r\n        END\r\n        IF ( @is_expiration_checked IS NOT NULL )\r\n        BEGIN\r\n          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked\r\n        END\r\n    END\r\n    IF (@denylogin = 1)\r\n    BEGIN -- login is denied access\r\n      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )\r\n    END\r\n    ELSE IF (@hasaccess = 0)\r\n    BEGIN -- login exists but does not have access\r\n      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )\r\n    END\r\n    IF (@is_disabled = 1)\r\n    BEGIN -- login is disabled\r\n      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'\r\n    END\r\n    PRINT @tmpstr\r\n  END\r\n\r\n  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin\r\n   END\r\nCLOSE login_curs\r\nDEALLOCATE login_curs\r\nRETURN 0\r\nGO<\/code><\/pre>\n<p style=\"padding-left: 30px;\">2. Run<\/p>\n<pre><code>EXEC sp_help_revlogin<\/code><\/pre>\n<p>The output is the script that creates the logins with the original Security Identifier (SID) and the original password.<\/p>\n<p>G.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>MSDN is the best documentation in the world &#8211; read the original post here. Create\u00a0 helper stored procedures USE master GO IF OBJECT_ID (&#8216;sp_hexadecimal&#8217;) 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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10,13,9],"tags":[18],"_links":{"self":[{"href":"https:\/\/blog.georgebaciu.pro\/index.php?rest_route=\/wp\/v2\/posts\/264"}],"collection":[{"href":"https:\/\/blog.georgebaciu.pro\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.georgebaciu.pro\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.georgebaciu.pro\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.georgebaciu.pro\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=264"}],"version-history":[{"count":3,"href":"https:\/\/blog.georgebaciu.pro\/index.php?rest_route=\/wp\/v2\/posts\/264\/revisions"}],"predecessor-version":[{"id":267,"href":"https:\/\/blog.georgebaciu.pro\/index.php?rest_route=\/wp\/v2\/posts\/264\/revisions\/267"}],"wp:attachment":[{"href":"https:\/\/blog.georgebaciu.pro\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=264"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.georgebaciu.pro\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=264"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.georgebaciu.pro\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=264"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}