Transfers the login account of one database to another (Trans logins from one database to another)

Objective: To transfer A login account of A database to another account. Method: 1. Execute the next script USE master in database.
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
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

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

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 2. This script will beMasterCreate two stored procedures in the database. The two stored procedures are namedSp_hexadecimalStored Procedures andSp_help_revloginStored procedure. 3. Execute EXEC sp_help_revlogin. Save the generated script to the second server.