Copy a SQL login without knowing the password

28 Sep 2019 21800 views 0 minutes to read Contributors

Situation

You dont know the password of a login but you need to copy it to a destination server. 

Solution

You do not need to know the password of a login. You can copy the password (encrypted) to create it on the destination server.

Script

We create the script on the source server. We include checks to see if the login already exists when we create it on the destination server. We will take the encrypted password (convert it to NVARCHAR(MAX) and the SID to make sure they are the same on source and destination server (in case you want to copy the databases also, the security stays the same)

		
			
T-SQL
			
Transact-SQL
			

			
SELECT 'IF(SUSER_ID('+QUOTENAME(SL.name,'''')+') IS NULL)
BEGIN
CREATE LOGIN '+QUOTENAME(SL.name)+' WITH PASSWORD = '+CONVERT(NVARCHAR(MAX),SL.password_hash,1)+' HASHED, SID = '+CONVERT(NVARCHAR(MAX),SL.sid,1)+'
END'
FROM sys.sql_logins AS SL
WHERE name not like '##%##' and name <> 'sa'
			

				

					

				

					
1
2
3
4
5
6
				
						
SELECT 'IF(SUSER_ID('+QUOTENAME(SL.name,'''')+') IS NULL)
BEGIN
CREATE LOGIN '+QUOTENAME(SL.name)+' WITH PASSWORD = '+CONVERT(NVARCHAR(MAX),SL.password_hash,1)+' HASHED, SID = '+CONVERT(NVARCHAR(MAX),SL.sid,1)+'
END'
FROM sys.sql_logins AS SL
WHERE name not like '##%##' and name <> 'sa'