Fix orphaned SQL user logins on new server

This piece of code helps in the situation where you have attached or restored a database onto a different server or instance from where it originated.

SQL will automatically re-apply permissions for any windows users as the sid is unique across the domain, but for any SQL users you will find that even if the same names exist on the new server, they will not have the required access.

To fix this, you need to use sp_change_users_login with @Action of Auto_fix - this will remap the user sid in the database, to the login sid for this SQL instance.

You can run this manually for each affected login as follows - e.g. for login "SQLUser":
EXEC sp_change_users_login @Action = 'Auto_Fix', @UserNamePatter = 'SQLUser'

If you are restoring a lot of databases (e.g. into a Dev environment) this can be a lot of work though - so here is a script that will automatically Auto-Fix all orphaned users in either a selected, or all databases (using the cunning system proc sp_MSforeachdb) ... I bring you sp_maplogins.

CREATE proc [dbo].[sp_MapLogins] @Database varchar(50) = NULL, -- DatabaseName, ALL @Report bit = 1 -- 0 to ignore messages / results AS SET NOCOUNT ON DECLARE @SQL Varchar(1000), @DBName varchar(100), @UserName varchar(100) Create Table #Orphan (DBName varchar(100), UserName varchar(100), Remap Tinyint) IF @Database IS NULL IF DB_NAME() = 'master' SET @Database = 'ALL' ELSE SET @Database = DB_Name() IF @Database = 'ALL' EXEC sp_MSForEachDB ' Use [?] Insert #Orphan Select DB_Name(), U.name, CASE WHEN U.sid = L.sid THEN 0 WHEN Isnull(U.sid, 0) <> L.sid Then 1 ELSE 99 END FROM sysusers U LEFT Join master.dbo.syslogins L on U.name = L.name WHERE issqlrole = 0 and uid > 4 ' ELSE BEGIN SET @SQL = 'Insert #Orphan Select ''' + @Database + ''', U.name, CASE WHEN U.sid = L.sid THEN 0 WHEN Isnull(U.sid, 0) <> L.sid Then 1 ELSE 99 END FROM ' + @Database + '.dbo.sysusers U LEFT Join master.dbo.syslogins L on U.name = L.name WHERE issqlrole = 0 and uid > 4' EXEC (@SQL) END IF EXISTS (Select Top 1 1 From #Orphan Where Remap = 1) BEGIN DECLARE Remap Cursor FOR Select DBName, UserName From #Orphan Where Remap = 1 Open Remap Fetch Next From Remap Into @DBName, @UserName While @@Fetch_Status = 0 BEGIN SET @SQL = 'USE ' + @DBName + ' EXEC sp_change_users_login @Action = ''Auto_Fix'', @UserNamePattern = ''' + @UserName + '''' EXEC (@SQL) Fetch Next From Remap Into @DBName, @UserName END Close Remap Deallocate Remap END IF @Report = 1 BEGIN IF EXISTS (Select top 1 1 From #Orphan Where Remap = 99) BEGIN Print 'Unable to match all database users to server logins in ' + CASE WHEN @Database = 'ALL' Then 'all databases' ELSE 'database ' + @Database END END IF NOT Exists (Select * From #Orphan Where Remap > 0) Print 'No database users need remapping to server logins in ' + CASE WHEN @Database = 'ALL' Then 'all databases' ELSE 'database ' + @Database END ELSE select DBName AS "Database", UserName , "Action" = CASE WHEN Remap = 1 Then 'Remapped to SysLogin' WHEN Remap = 99 Then 'No corresponding SysLogin' Else '' END from #Orphan where Remap > 0 END