MS SQL Transfer Logins

With any migration or database move, you want to transfer the logins but only transfer those logins that are still valid. After finding several different ways not to do what I want, I have put together one script that will hopefully achieve the required outcome. The script has been tested on MSSQL versions:

  • 2005
  • 2008
  • 2012

Setup

1. Set default database to master for users where the database no longer exists

SELECT 'ALTER LOGIN [' + [name] + '] WITH DEFAULT_DATABASE = master ' 
FROM master.sys.server_principals
WHERE default_database_name not in (select name from SYS.databases)

2. Run the output.

3. Copy and Paste the following script:

-- edit the script where mentioned i.e -- change to number 9 for 2005, 1 for 2008, 1 for 2012
-- copy each section into notepad
-- there should be 4 sections in the results window
-- call this file "all_users_version_instance.sql"
USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- Drop function if already exists IF object_id(N'fn_hexadecimal', N'FN') IS NOT NULL DROP FUNCTION fn_hexadecimal GO -- Create function for passwords CREATE FUNCTION [dbo].[fn_hexadecimal] ( @binvalue varbinary(256) ) RETURNS VARCHAR(256) AS BEGIN DECLARE @charvalue varchar(256) 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 return @charvalue END GO BEGIN IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)), 1) = '1' -- change to number 9 for 2005, 1 for 2008, 1 for 2012 IF EXISTS (SELECT TOP 1 * FROM Tempdb.sys.objects (nolock) WHERE name LIKE '#TUser%') DROP TABLE #TUser ELSE IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)), 1) = '8' -- for SQL 2000 IF EXISTS (SELECT TOP 1 * FROM Tempdb.dbo.sysobjects (nolock) WHERE name LIKE '#TUser%') DROP TABLE #TUser -- create temporary table to hold user logins for all databases CREATE TABLE #tuser ( ServerName varchar(256), DBName SYSNAME, [Name] SYSNAME, GroupName SYSNAME NULL, LoginName SYSNAME NULL, default_database_name SYSNAME NULL, default_schema_name VARCHAR(256) NULL, Principal_id INT, sid VARBINARY(85) ) -- populate temporary table IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)), 1) = '8' -- for SQL 2000 INSERT INTO #TUser EXEC sp_MSForEachdb ' SELECT @@SERVERNAME, ''?'' as DBName, u.name As UserName, CASE WHEN (r.uid IS NULL) THEN ''public'' ELSE r.name END AS GroupName, l.name AS LoginName, NULL AS Default_db_Name, NULL as default_Schema_name, u.uid, u.sid FROM [?].dbo.sysUsers u LEFT JOIN ([?].dbo.sysMembers m JOIN [?].dbo.sysUsers r ON m.groupuid = r.uid) ON m.memberuid = u.uid LEFT JOIN dbo.sysLogins l ON u.sid = l.sid WHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1 /*and u.name like ''tester''*/ ORDER BY u.name ' -- populate temporary table ELSE IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)), 1) = '1' -- change to number 9 for 2005, 1 for 2008, 1 for 2012 INSERT INTO #TUser EXEC sp_MSForEachdb ' SELECT @@SERVERNAME, ''?'', u.name, CASE WHEN (r.principal_id IS NULL) THEN ''public'' ELSE r.name END GroupName, l.name LoginName, l.default_database_name, u.default_schema_name, u.principal_id, u.sid FROM [?].sys.database_principals u LEFT JOIN ([?].sys.database_role_members m JOIN [?].sys.database_principals r ON m.role_principal_id = r.principal_id) ON m.member_principal_id = u.principal_id LEFT JOIN [?].sys.server_principals l ON u.sid = l.sid WHERE u.TYPE <> ''R'' /*and u.name like ''tester''*/ order by u.name ' -- Now produce valid logins PRINT '-----------------------------------------------------------------------------' PRINT '-- Create the windows logins' PRINT '-----------------------------------------------------------------------------' SELECT 'IF NOT EXISTS (SELECT * FROM master.sys.server_principals WHERE [name] = ''' + [name] + ''') CREATE LOGIN [' + [name] + '] FROM WINDOWS WITH DEFAULT_DATABASE=[' + default_database_name + '], DEFAULT_LANGUAGE=[us_english] GO ' FROM master.sys.server_principals where type_desc In ('WINDOWS_GROUP', 'WINDOWS_LOGIN') AND [name] not like 'BUILTIN%' and [NAME] not like 'NT AUTHORITY%' and [name] not like '%\SQLServer%' and LOWER(name) in (SELECT distinct(LOWER(Name)) FROM #TUser where Name not in ('dbo','guest','sys','INFORMATION_SCHEMA')) PRINT '-----------------------------------------------------------------------------' PRINT '-- Create the SQL Logins' PRINT '-----------------------------------------------------------------------------' select 'IF NOT EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''') CREATE LOGIN [' + [name] + '] WITH PASSWORD=' + [master].[dbo].[fn_hexadecimal](password_hash) + ' HASHED, SID = ' + [master].[dbo].[fn_hexadecimal]([sid]) + ', DEFAULT_DATABASE=[' + default_database_name + '], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=' + CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_POLICY=OFF GO IF EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''') ALTER LOGIN [' + [name] + '] WITH CHECK_EXPIRATION=' + CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_POLICY=' + CASE WHEN is_policy_checked = 1 THEN 'ON' ELSE 'OFF' END + ' GO ' from master.sys.sql_logins where type_desc = 'SQL_LOGIN' and LOWER(name) in (SELECT distinct(LOWER(Name)) FROM #TUser where Name not in ('dbo','guest','sys','INFORMATION_SCHEMA')) and [name] not in ('sa', 'guest') PRINT '-----------------------------------------------------------------------------' PRINT '-- Disable any logins' PRINT '-----------------------------------------------------------------------------' SELECT 'ALTER LOGIN [' + [name] + '] DISABLE GO ' from master.sys.server_principals where is_disabled = 1 and LOWER(name) in (SELECT distinct(LOWER(Name)) FROM #TUser where Name not in ('dbo','guest','sys','INFORMATION_SCHEMA')) PRINT '-----------------------------------------------------------------------------' PRINT '-- Assign groups' PRINT '-----------------------------------------------------------------------------' select 'EXEC master..sp_addsrvrolemember @loginame = N''' + l.name + ''', @rolename = N''' + r.name + ''' GO ' from master.sys.server_role_members rm join master.sys.server_principals r on r.principal_id = rm.role_principal_id join master.sys.server_principals l on l.principal_id = rm.member_principal_id where l.[name] not in ('sa') AND l.[name] not like 'BUILTIN%' and l.[NAME] not like 'NT AUTHORITY%' and l.[name] not like '%\SQLServer%' and LOWER(l.name) in (SELECT distinct(LOWER(Name)) FROM #TUser where Name not in ('dbo','guest','sys','INFORMATION_SCHEMA')) DROP TABLE #TUser END

 

4. Edit the file "all_users_version_instance.sql" and make sure that " GO " is on a new line e.g in Notepad++ replace " GO " with \nGO\n 

5. Save, paste it into sql editor, parse it and then run it.