/* NJohnson9402 / natethedba.wordpress.com
An example of how to check for orphaned database users (users that do not map to a server login).
Basically the idea here is to take the output of `sys.sp_helpuser` and utilize it to find & fix.
There are much cooler ways out there, such as dbatools.io/functions/repair-dbaorphanuser/, but
I have occasionally seen these not work in odd situations.  Plus, if your login names and user names
don't exactly match, this method could be extended to enter custom mappings-- you'd simply add a new
column to #HelpUser, say [NewLoginName], and use that in the 'fix-it' statement-generator query.
*/
USE MyDatabase;

--For storing the output of sys.sp_helpuser
IF (OBJECT_ID('tempdb.dbo.#HelpUser') IS NOT NULL)
    DROP TABLE #HelpUser;

CREATE TABLE #HelpUser (
    [UserName] nvarchar(44)
    , [RoleName] nvarchar(16)
    , [LoginName] nvarchar(44)
    , [DefDBName] nvarchar(12)
    , [DefSchemaName] nvarchar(6)
    , [UserID] char(10)
    , [SID] varbinary(85)
);

--Get the output
INSERT #HelpUser
EXEC sys.sp_helpuser;

--Remove "known system users" (we don't care about them)
DELETE FROM #HelpUser
WHERE UserName IN ('dbo', 'guest', 'sys', 'INFORMATION_SCHEMA')
OR UserName LIKE 'NT AUTHORITY\%' OR UserName LIKE 'BUILTIN\%'

--Unique-ify, & remove the role column
DELETE hu
FROM #HelpUser hu
JOIN (SELECT UserName, MaxRole = MAX(RoleName)
    FROM #HelpUser
    GROUP BY UserName
) hu2
    ON hu.UserName = hu2.UserName
    AND hu.RoleName < hu2.MaxRole

ALTER TABLE #HelpUser
DROP COLUMN RoleName;

--Add a flag for "type" (SQL login vs. Windows)
ALTER TABLE #HelpUser
ADD UserType varchar(5);

--Populate said flag
UPDATE hu SET hu.UserType = (CASE WHEN usr.issqluser = 1 THEN 'SQL' WHEN usr.isntname = 1 then 'WIN' ELSE '?' END)
FROM #HelpUser hu
JOIN sys.sysusers usr
    ON hu.SID = usr.sid

--Find the users w/ a NULL LoginName (i.e. not mapped to a Login)
SELECT * FROM #HelpUser
WHERE LoginName IS NULL

--Generate "fix statements", assuming name-matching
/*
SELECT FixIt = 'ALTER USER ' + QUOTENAME(hu.UserName) + ' WITH LOGIN = ' + QUOTENAME(hu.UserName) + ';'
FROM #HelpUser hu
JOIN master.sys.syslogins sl
    ON hu.UserName = sl.name
WHERE hu.LoginName IS NULL
*/

DECLARE @Sql varchar(MAX);
SELECT @Sql = STUFF((
    SELECT ';' + CHAR(10) + 'ALTER USER ' + QUOTENAME(hu.UserName) + ' WITH LOGIN = ' + QUOTENAME(hu.UserName)
    FROM #HelpUser hu
    JOIN master.sys.syslogins sl
        ON hu.UserName = sl.name
    WHERE hu.LoginName IS NULL
    FOR XML PATH('')
), 1, 2, '') + ';'

PRINT (@Sql);
--EXEC (@Sql);  --UNCOMMENT this line to actually execute the fixes!


--Check again to see if we've fixed the problem!
--Store in a different temp-table just in case we want to compare
IF (OBJECT_ID('tempdb.dbo.#FixedUser') IS NOT NULL)
    DROP TABLE #FixedUser;

CREATE TABLE #FixedUser (
    [UserName] nvarchar(44)
    , [RoleName] nvarchar(16)
    , [LoginName] nvarchar(44)
    , [DefDBName] nvarchar(12)
    , [DefSchemaName] nvarchar(6)
    , [UserID] char(10)
    , [SID] varbinary(85)
);

--Get the output
INSERT #FixedUser
EXEC sys.sp_helpuser;

--Remove "known system users" (we don't care about them)
DELETE FROM #FixedUser
WHERE UserName IN ('dbo', 'guest', 'sys', 'INFORMATION_SCHEMA')
OR UserName LIKE 'NT AUTHORITY\%' OR UserName LIKE 'BUILTIN\%'

--Check for unmapped
SELECT * FROM #FixedUser
WHERE LoginName IS NULL