none
How to find user databases with no users? RRS feed

  • Question

  • Can anyone recommend a script I can run against a group of SQL server instances which will return a list of user databases (excluding system db's) which have no users in them?  Ideally, this should exclude public, dbo, guest, sys & INFORMATION_SCHEMA, since those are default users present in all databases.

    The purpose of this report would be to help identify databases that may no longer be used (orphaned database) because they don't have any database users in them. Understanding, of course, that some Logins with certain instance-level server roles, can access databases, even though they may not map to a user at the database level.

    Thanks in advance,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Thursday, May 9, 2019 1:16 PM
    Wednesday, May 8, 2019 7:54 PM

All replies

  • Well, here's the best I could come up with on my own, so far:

    DECLARE @command varchar(1000) 
    SELECT @command = 'USE ? IF DB_ID("?") >4 SELECT ''?'', COUNT(name) as [db_users] FROM sys.sysusers WHERE status <> 0 HAVING COUNT(name) = 0'
    EXEC sp_MSforeachdb @command 

    If anybody else has anything better, or can improve on my code, please feel free to share.

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Thursday, May 9, 2019 3:47 PM
    Thursday, May 9, 2019 2:50 PM