1

I have a Microsoft SQL Server that had a database called SQLDATABASE. It also had a user called SQLUSER. The SQLDATABASE was deleted but not SQLUSER. SQLUSER only belonged to SQLDATABASE. I want to know all of the "Broken" users (e.g. SQLUSER) that is not part of a SQL database so that I can "clean house". I can go into every sql user and delete them by looking to see if they are attached but that seems very inefficient and something a script can help with. This is what I am asking.

The server has 200 SQL Databases but 250 SQL Users. I want to remove the extra 50 SQL Users as they aren't being used and there is no reason to have them.

I am trying to find a script to either let me find (and delete) these SQL Users or at least identify so I don't have to go into each of the SQL Users individually.

  • 3
    Do you mean the login SQLUSER was left behind? A login exists at the server level, a user exists inside a database. If you drop a database, a user can’t be left behind, but a login can. – Aaron Bertrand Apr 10 at 2:34
3

There seems to be some confusion over terminology. A user exists at the database level and is removed when you drop a database. A login exists at the server level and is mapped to a database user to get access to the database.

I'm assuming you want to find all logins that are not mapped to a database user and are therefore potentially no longer in use. The below query will achieve that:

CREATE TABLE ##UnusedLogins (
    [Login] NVARCHAR(255),
    [InUse] BIT
)

INSERT INTO ##UnusedLogins ([Login], [InUse])
SELECT [login].[name] AS [Login],
    0 AS [InUse]
FROM sys.server_principals [login]
WHERE [login].[name] NOT LIKE 'NT SERVICE\%'
    AND [login].[name] NOT LIKE 'NT AUTHORITY\%'
    AND [login].[name] NOT LIKE '##%'
    AND [login].[type_desc] <> 'SERVER_ROLE'
    AND [login].[name] <> 'sa'

EXEC sp_msforeachdb 'UPDATE ul
SET [InUse] = 1
FROM ##UnusedLogins ul
INNER JOIN sys.server_principals [login] ON [login].[name] = ul.[Login]
INNER JOIN [?].sys.database_principals [user] ON [user].[sid] = [login].[sid]'

SELECT * FROM ##UnusedLogins

DROP TABLE ##UnusedLogins

Any login in the output that has a 1 in the InUse column is mapped to a database user. Any login with a 0 is not mapped. This does not mean the login is not in use, simply that it is not mapped to any database user.

  • 1
    Please be aware sp_msforeachdb is undocumented, unsupported, and severely broken. See here, here, and here for better alternatives. – Aaron Bertrand Apr 11 at 1:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.