At times we need to replicate the same database from one server to another, after moving users may not be able to use the database and receive the error as
This may be due to unavailable of logins and passwords on the instance to which the database has been moved. To transfer the logins and passwords from one instance to another follow the below steps,
Login failed for user ‘TestUser’, (Microsoft SQL Server, Error: 18456)
This may be due to unavailable of logins and passwords on the instance to which the database has been moved. To transfer the logins and passwords from one instance to another follow the below steps,
· Connect to the instance from where the database has moved.
· Copy the below scripts into the query window and execute
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 authentications
-- 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
· The above script creates two stored procedures in the master database, they are sp_hexadecimal and the sp_help_revlogin stored procedures.
· Once the stored procedure gets created, execute it in a new query editor window.
EXEC sp_help_revlogin
· The output script that is generated by the sp_help_revlogin stored procedure is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.
· Start SQL Server Management Studio and then connect to the instance of SQL Server to which the database has moved
· Open a new Query Editor window, and then run the output script that is generated. you can edit the script to avoid creating the redundant logins. Before run the script to the destination server read the below points.
Note:· Assess the output script cautiously. If server 1 and server 2 are in different domains, you will have to modify the output script and replace the original domain name with the new domain name in the CREATE LOGIN statements. The integrated logins that are granted access in the new domain do not have the same SID as the logins in the original domain. Therefore, the users are orphaned from these logins. To resolve the permission problem, refer this link, http://support.microsoft.com/kb/240872
· The script does not transfer the default database information for a particular login, because the default database may not always exist on server 2. To define the default database for a login, use the ALTER LOGIN statement by passing in the login name and the default database as arguments.
· The sort order of both the servers may be case sensitive, or the sort order of both the servers may be case insensitive. In these cases, the users do not experience a problem. If not, users will experience a problem.
