Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/* 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment