Creating SQL Server login using hashed password

When we restore a database in target instance, along with database we need to fix the Orphaned logins.

If target instance has the respective login we can directly fix the orphaned users using below query:

exec sp_change_users_login 'auto_fix' or
exec sp_change_users_login 'update_one'


If the login does not exists on the target instance we need to copy that login from Source to target.
As all the passwords for SQL logins are in encrypted mode, we cannot see that password. For this type of situation we can copy the logins by fetching the hashed password, using below query


--- To generate Hashed paaword

SELECT LOGINPROPERTY('Loginname','PASSWORDHASH')




-- To create the login




CREATE LOGIN [Login Name] WITH PASSWORD = [Hashed password] HASHED;<br />

This will create the login with same password as in source instance.

Now you can proceed with fixing the orphaned users.