SQL Server- Migrate Logins from One server to another server

There is a Microsoft script which we can run it in the source server and script out logins of the source server and run it in the target server.

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 <= @length ) 
      BEGIN 
          DECLARE @tempint INT 
          DECLARE @firstint INT 
          DECLARE @secondint INT 
 
          SELECT @tempint = CONVERT(INT, Substring(@binvalue, @i, 1)) 
 
          SELECT @firstint = Floor(@tempint / 16) 
 
          SELECT @secondint = @tempint - ( @firstint * 16 ) 
 
          SELECT @charvalue = @charvalue 
                              + Substring(@hexstring, @firstint+1, 1) 
                              + Substring(@hexstring, @secondint+1, 1) 
 
          SELECT @i = @i + 1 
      END 
 
    SELECT @hexvalue = @charvalue 
 
go 
 
IF Object_id ('sp_help_revlogin') IS NOT NULL 
  DROP PROCEDURE sp_help_revlogin 
 
go 
 
CREATE PROCEDURE Sp_help_revlogin @login_name SYSNAME = NULL 
AS 
    DECLARE @name SYSNAME 
    DECLARE @type VARCHAR (1) 
    DECLARE @hasaccess INT 
    DECLARE @denylogin INT 
    DECLARE @is_disabled INT 
    DECLARE @PWD_varbinary VARBINARY (256) 
    DECLARE @PWD_string VARCHAR (514) 
    DECLARE @SID_varbinary VARBINARY (85) 
    DECLARE @SID_string VARCHAR (514) 
    DECLARE @tmpstr VARCHAR (1024) 
    DECLARE @is_policy_checked VARCHAR (3) 
    DECLARE @is_expiration_checked VARCHAR (3) 
    DECLARE @defaultdb SYSNAME 
 
    IF ( @login_name IS NULL ) 
      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 <> '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

How to run this script:

  1. Run this T-SQL script in the master
  2. After the two procedures is created in master database, You can run the procedure using the command Exec Sp_help_revlogin
  3. This script will generate the login script with password
  4. Run the loginscript in the target server.

SQL SERVER 2016 – CTP Released

SQL Server 2016 CTP has been released by Microsoft.  SQL Server 2016 comes with In- Memory enhancements and Advance Analytics.  The couple of enhancements for SQL Server are related to performance, security upgrades and scalability. You will get more information about the new version from the datasheet.

You can download SQL Server 2016 Datasheet from here.

 

You can also download SQL Server 2016 CTP Version from below link.

http://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

SQL Server – Database Migration – Move Database from One server to another Server

Pre-requisites:

  • Analyze the disk space in the target server.
  • Confirm the data and log file location in the target server.
  • Collect the information about database properties
  • Collect the information about the application dependencies
  • Collect information about the logins and & users
  • Check the database for Orphan users
  • Check the SQL Server for dependent objects (SQL jobs , Linked Servers)
  • Check the Maintenance Plan for the database

Database Migration Checklist:

  • Stop the application services
  • Change the database to read-only mode (Optional)
  • Take the latest backup of all the databases involved in migration
  • Restore the databases on the target server on the appropriate drives
  • Cross check the database properties as per the database property script output, change the database properties as per the pre migration- checklist
  • Execute the output of Login transfer script on the target server, to create logins on the target server
  • Check for Orphan Users and Fix Orphan Users
  • Execute DBCC UPDATEUSAGE on the restored database.
  • Rebuild Indexes (Optional) as per the requirement and time window you can execute this option.
  • Update Index statistics
  • Re-compile Procedures
  • Start the application services, check the application functionality and check the Windows event logs.
  •  Check the SQL Server Error Log for login failures and other errors