Created
January 11, 2018 17:44
-
-
Save NJohnson9402/82c2518401543fdcaa644da6be5ab731 to your computer and use it in GitHub Desktop.
See corresponding blog post at https://natethedba.wordpress.com/t-sql-tuesday-98-orphaned-users-redux/
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* 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