AKAWN

You may come across the need to migrate one or all logins to another instance.

Here are some scripts that may be able to assist you with that task without you needing to know the login passwords.

Remember to review and exclude the logins you don’t need to transfer.

1. Script all SQL Logins

-- SQL Server 2008 onwards
SELECT 
  'CREATE LOGIN [' + [name] + '] WITH PASSWORD='
+ CONVERT(varchar(MAX),password_hash,1)
+ ' HASHED, DEFAULT_DATABASE=[' + default_database_name 
+ '], DEFAULT_LANGUAGE=[' + default_language_name 
+ '], CHECK_EXPIRATION=' + CASE WHEN is_expiration_checked = 0 THEN 'OFF' ELSE 'ON' END
+ ', CHECK_POLICY=' + CASE WHEN is_policy_checked = 0 THEN 'OFF' ELSE 'ON' END
+ ', SID='
+ CONVERT(varchar(MAX),sid,1)
+ '; ' + CASE WHEN is_disabled = 1 THEN 'ALTER LOGIN [' + name + '] DISABLE;' ELSE '' END
FROM master.sys.sql_logins
WHERE 1=1
AND [name] NOT LIKE '##%'
AND [name] <> 'sa'
ORDER BY [name];

-- Pre SQL Server 2008
SELECT 
  'CREATE LOGIN [' + [name] + '] WITH PASSWORD='
+ master.dbo.fn_varbintohexstr(password_hash)
+ ' HASHED, DEFAULT_DATABASE=[' + default_database_name 
+ '], DEFAULT_LANGUAGE=[' + default_language_name 
+ '], CHECK_EXPIRATION=' + CASE WHEN is_expiration_checked = 0 THEN 'OFF' ELSE 'ON' END
+ ', CHECK_POLICY=' + CASE WHEN is_policy_checked = 0 THEN 'OFF' ELSE 'ON' END
+ ', SID='
+ master.dbo.fn_varbintohexstr(sid)
+ '; ' + CASE WHEN is_disabled = 1 THEN 'ALTER LOGIN [' + name + '] DISABLE;' ELSE '' END
FROM master.sys.sql_logins
WHERE 1=1
AND [name] NOT LIKE '##%'
AND [name] <> 'sa'
ORDER BY [name];

2. Script the sa password to match on the other instance, if required

-- SQL Server 2008 onwards
SELECT 
  'IF (SELECT is_policy_checked FROM master.sys.sql_logins WHERE [name]=''sa'') = 1'
+ ' BEGIN'
+ ' ALTER LOGIN [sa] WITH CHECK_POLICY=OFF, PASSWORD = '	
+ CONVERT(varchar(MAX),password_hash,1)
+ ' HASHED; ALTER LOGIN [sa] WITH CHECK_POLICY=ON;'
+ ' END'
+ ' ELSE'
+ ' BEGIN'
+ ' ALTER LOGIN [sa] WITH PASSWORD =' 
+  CONVERT(varchar(MAX),password_hash,1)
+ ' HASHED;'
+ ' END;'
FROM master.sys.sql_logins
WHERE 1=1
AND [name] = 'sa';

-- Pre SQL Server 2008
SELECT 
  'IF (SELECT is_policy_checked FROM master.sys.sql_logins WHERE [name]=''sa'') = 1'
+ ' BEGIN'
+ ' ALTER LOGIN [sa] WITH CHECK_POLICY=OFF, PASSWORD = '	
+ master.dbo.fn_varbintohexstr(password_hash)
+ ' HASHED; ALTER LOGIN [sa] WITH CHECK_POLICY=ON;'
+ ' END'
+ ' ELSE'
+ ' BEGIN'
+ ' ALTER LOGIN [sa] WITH PASSWORD =' 
+  master.dbo.fn_varbintohexstr(password_hash)
+ ' HASHED;'
+ ' END;'
FROM master.sys.sql_logins
WHERE 1=1
AND [name] = 'sa';

3. Script the Windows logins

SELECT
  'CREATE LOGIN [' + [name] + '] FROM WINDOWS WITH DEFAULT_DATABASE=[' 
+ COALESCE(default_database_name,'master')
+ '], DEFAULT_LANGUAGE=[' + COALESCE(default_language_name,'us_english')
+ ']; ' + CASE WHEN is_disabled = 1 THEN 'ALTER LOGIN [' + name + '] DISABLE;' ELSE '' END
FROM master.sys.server_principals
WHERE 1=1
AND type_desc IN ('WINDOWS_LOGIN','WINDOWS_GROUP')
ORDER BY [name];

4. Script the server role memberships of the logins

-- SQL Server 2012 onwards
SELECT
 'ALTER SERVER ROLE [' + a.[name] + '] ADD MEMBER [' + b.[name] + '];'
FROM master.sys.server_principals a, master.sys.server_principals b, master.sys.server_role_members c
WHERE 1=1
AND a.principal_id = c.role_principal_id
AND b.principal_id = c.member_principal_id
ORDER BY a.name, b.name;

-- Pre SQL Server 2012
SELECT
 'EXEC sp_addsrvrolemember [' + b.[name] + '],[' + a.[name] + '];'
FROM master.sys.server_principals a, master.sys.server_principals b, master.sys.server_role_members c
WHERE 1=1
AND a.principal_id = c.role_principal_id
AND b.principal_id = c.member_principal_id
ORDER BY a.[name], b.[name];

5. Script the server securables

SELECT a.state_desc + ' ' + a.[permission_name] + ' TO [' + b.name + ']' COLLATE Latin1_General_CI_AS AS server_securables
FROM sys.server_permissions a, sys.server_principals b
WHERE 1=1
AND a.grantee_principal_id = b.principal_id
AND a.[permission_name] NOT IN ('CONNECT SQL')
AND b.name NOT LIKE '##%'
AND b.name <> 'public'
ORDER BY b.name;

 
When a database is restored the users should automatically resolve to the logins you’ve transferred, unless they were orphaned to being with.
 
The following script shows which database users are orphaned.

DECLARE @orphaned_users TABLE (DatabaseName nvarchar(128), UserName nvarchar(128));

DECLARE item_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT [name] FROM sys.databases WHERE state_desc = 'ONLINE'
OPEN item_cursor
DECLARE @cursor nvarchar(128)
DECLARE @sql nvarchar(MAX);
FETCH NEXT FROM item_cursor INTO @cursor
WHILE @@FETCH_STATUS = 0 
BEGIN

IF (CAST(SERVERPROPERTY('ProductVersion') AS varchar(10))) NOT LIKE '9%'
AND (CAST(SERVERPROPERTY('ProductVersion') AS varchar(10))) NOT LIKE '10%'
BEGIN
SET @sql = 'USE [' + @cursor + '];
SELECT ''' + @cursor + ''', UserName = [name]
FROM sysusers  
WHERE 1=1
AND  issqluser = 1   
AND (sid IS NOT NULL AND sid <> 0x0)  
AND (LEN(sid) <= 16)  
AND SUSER_SNAME(sid) IS NULL
UNION
SELECT ''' + @cursor + ''', UserName = [name]
FROM sys.database_principals
WHERE 1=1
AND authentication_type_desc = ''WINDOWS''
AND  [name] NOT LIKE ''dbo''
EXCEPT
(SELECT ''' + @cursor + ''',DP.[name] FROM sys.database_principals DP
INNER JOIN sys.server_principals SP ON DP.[sid] = SP.[sid] 
WHERE 1=1
AND DP.authentication_type_desc = ''WINDOWS'')';
END
ELSE
BEGIN
SET @sql = 'USE [' + @cursor + '];
SELECT ''' + @cursor + ''', UserName = [name]
FROM sysusers  
WHERE 1=1
AND  issqluser = 1   
AND (sid IS NOT NULL AND sid <> 0x0)  
AND (LEN(sid) <= 16)  
AND SUSER_SNAME(sid) IS NULL
UNION
SELECT ''' + @cursor + ''', UserName = [name]
FROM sys.database_principals
WHERE 1=1
AND type_desc IN (''WINDOWS_USER'',''WINDOWS_GROUP'')
AND  [name] NOT LIKE ''dbo''
EXCEPT
(SELECT ''' + @cursor + ''',DP.[name] FROM sys.database_principals DP
INNER JOIN sys.server_principals SP ON DP.[sid] = SP.[sid] 
WHERE 1=1
AND DP.type_desc IN (''WINDOWS_USER'',''WINDOWS_GROUP''))';

INSERT INTO @orphaned_users (DatabaseName, UserName)
EXEC(@sql);
END

FETCH NEXT FROM item_cursor INTO  @cursor
END
CLOSE item_cursor;  
DEALLOCATE item_cursor; 

SELECT DatabaseName AS database_name, UserName AS orphaned_user
FROM @orphaned_users ORDER BY DatabaseName,UserName;