How to fix orphaned SQL Server users

First, make sure that this is the problem. This will lists the orphaned users:

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user'

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

I have also written a script that will remove any users that don't have SQL Server Logins from the Database, and fix any that are orphaned that do have SQL Logins. This script needs to be run with EXTREME caution:

USE [Database Name]

DECLARE @Login    VARCHAR(128)

DECLARE @SQL      VARCHAR(1000)

DECLARE @SQL2     VARCHAR(1000)

DECLARE @SQL3     VARCHAR(1000)

CREATE TABLE #LoginsTable

      (UserName VARCHAR(128)

      ,UserSID VARCHAR(128));

INSERT INTO #LoginsTable

      EXEC sp_change_users_login 'Report';

ALTER TABLE #LoginsTable ADD [IsOrph] BIT;

UPDATE #LoginsTable

SET [IsOrph]=0;

WHILE (SELECT COUNT(*) FROM #LoginsTable WHERE IsOrph = 0) > 0

      BEGIN

            SELECT TOP 1 @Login = UserName

            FROM #LoginsTable

            WHERE IsOrph = 0;

                  IF @Login IN (SELECT name AS [Name]

                                          FROM sys.server_principals)

                        BEGIN

                              SET @SQL = 'EXEC sp_change_users_login ''Auto_Fix'', '''+@Login+''''

                              EXEC (@SQL)

                              PRINT 'Is a SQL Login ' + @Login

                        END

                  ELSE

                        BEGIN

                              SET @SQL2 = 'IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N''' + @Login + ''') DROP SCHEMA [' + @Login + ']'

                              EXEC (@SQL2)

                              SET @SQL3 = 'IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + @Login + ''') DROP USER [' + @Login + ']'

                              EXEC (@SQL3)

                              PRINT 'Is NOT a SQL Login ' + @Login

                        END

            UPDATE #LoginsTable

            SET IsOrph = 1

            WHERE UserName = @Login

      END

DROP TABLE #LoginsTable