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
You can’t perform that action at this time.