Transfer logins and passwords between instances of SQL Server

 

Transfer logins and passwords between instances of SQL Server

This article describes how to transfer the logins and the passwords between different instances of SQL Server running on Windows.

Original product version:   SQL Server
Original KB number:   918992

Introduction

This article describes how to transfer the logins and passwords between different instances of Microsoft SQL Server.

 Note

The instances may be on the same server or on different servers, and their versions may differ.

More information

In this article, server A and server B are different servers.

After you move a database from the instance of SQL Server on server A to the instance of SQL Server on server B, users may be unable to log in to the database on server B. Additionally, users may receive the following error message:

Login failed for user 'MyUser'. (Microsoft SQL Server, Error: 18456)

This problem occurs because you did not transfer the logins and the passwords from the instance of SQL Server on server A to the instance of SQL Server on server B.

 Note

The 18456-error message also occurs due to other reasons. For additional information on these causes and potential resolutions review MSSQLSERVER_18456.

To transfer the logins, use one of the following methods, as appropriate for your situation.

  • Method 1: Reset the password on the destination SQL Server computer (Server B)

    To resolve this issue, reset the password in SQL Server computer, and then script out the login.

     Note

    The password hashing algorithm is used when you reset the password.

  • Method 2: Transfer logins and passwords to destination server (Server B) using scripts generated on source server (Server A)

    1. Create stored procedures that will help generate necessary scripts to transfer logins and their passwords. To do this, connect to Server A using SQL Server Management Studio (SSMS) or any other client tool and run the following script:

      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 <= @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

       Note

      This script creates two stored procedures in the master database. The procedures are named sp_hexadecimal and sp_help_revlogin .

    2. In the SSMS query editor, select the Results to Text option.

    3. Run the following statement in the same or a new query window:

      SQL
      EXEC sp_help_revlogin

    4. The output script that the sp_help_revlogin stored procedure generates is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.

Comments

Post a Comment

Popular posts from this blog

CREATE VIEW SQL: Modifying views in SQL Server

Image
Introduction The primary goal will be to get familiar with the  ALTER VIEW  command used to modify views and change the output. A view is based on the result set from a query, and this command allows us to change the structure and definition of a query. Ironically, before modifying a view, we will create another view with a bit more complex T-SQL using aggregates in it rather than having a simple SELECT statement that is pulling everything from a table. We will not go over the syntax again since T-SQL is exactly the same as in CREATE VIEW SQL statement except the fact that instead of the CREATE reserved keyword ALTER is used. Creating view As I mentioned earlier, let’s use the code from below to create a bit more complex view: 1 2 3 4 5 6 7 8 9 10 11 CREATE VIEW vTop3SalesByQuantity AS      SELECT TOP 3 --will only return first 3 records from query      Sales . ProductID ,      Name AS ProductName ,      SUM ( Sales . Quantity ) AS TotalQuantity      FROM Sales            J
Read more

Syntaxes for Restore

 restore filelistonly from disk = 'E:\RAVIDBA\FULL\AdventureWorks2012_full_backup_20210529_120800.bak ' select * from sys.sysaltfiles restore database [AdventureWorks2012]  from disk = 'E:\RAVIDBA\FULL\AdventureWorks2012_full_backup_20210529_120800.bak'  with replace , recovery ; restore database [RAVIDBA]  from disk ='E:\RAVIDBA\FULL\AdventureWorks2012_full_backup_20210529_120800.bak' with move 'AdventureWorks2012'  to 'D:\MYSQLSERVER\MSSQL11.RAVISQLSERVERDBA\MSSQL\DATA\RAVIDBA.mdf',  move 'AdventureWorks2012_log'  to 'D:\MYSQLSERVER\MSSQL11.RAVISQLSERVERDBA\MSSQL\LogDB\RAVIDBA_log.ldf' , replace, recovery , stats = 5 ;
Read more

SQL SERVER –Different Ways to Find Recovery Model for Database

Image
  This is a very easy method and it gives all the database information in one script. SELECT  name  AS  [Database Name] , recovery_model_desc  AS  [Recovery Model]  FROM  sys.databases GO Method 4 This method provides only one database at a time. SELECT  'ADVENTUREWORKS'  AS  [Database Name] , DATABASEPROPERTYEX ( 'ADVENTUREWORKS' ,  'RECOVERY' ) AS  [Recovery Model] GO My recommendation
Read more