Get number total User counts for each DB within SQL Server instance ?

techresearch7777777 1,766

Hello, can someone provide me a query to list all User DBs (not including master, model, msdb, tempd) within a SQL Server instance and their number of Total Users counts (not including system dbo, guest, INFORMATION SCHEMA, sys) that has access to each DB?

Output would be something like this:

[Database Name] [Total Users]

Products = 3

Sales = 7

Invoices = 11

etc...

Am thinking GROUP BY would be used along those lines.

Thanks in advance.

techresearch7777777 1,766 Reputation points

2023-05-17T22:10:48.55+00:00

Does anyone know or point me in any direction that I can achieve this. Thanks in advance.

techresearch7777777 1,766

I came across a script on a website which uses dynamic SQL and I tweaked it to simplify which it does successfully return all the individual UserNames for all DBs that they have permissions to within a SQL Server instance (obviously a UserName can be listed in multiple DBs) rows having just 2 needed Columns which are [DBName] & [UserName] in following.

But I am stuck on how to have the UserName Column to do a total number of Users within each DB to make it into a consolidated output query where I can copy/paste somewhere else?

I tried adding a GROUP BY but it fails.

Any help tweaking my query would be appreciated, thanks.


USE master
GO
BEGIN

    IF EXISTS (SELECT TOP 1 *
               FROM tempdb.sys.objects (nolock)
               WHERE name LIKE '#TUser%')
        DROP TABLE #TUser


CREATE TABLE #TUser (
    DBName        SYSNAME,
    [Name]        SYSNAME)

INSERT INTO #TUser
EXEC sp_MSforeachdb 
'
 SELECT 
   ''?'' as DBName,
   u.name As UserName
 FROM [?].dbo.sysusers u
 WHERE u.name not in (
 ''dbo'',
 ''guest'',
''db_accessadmin'',
''db_backupoperator'',
''db_datareader'',
''db_datawriter'',
''db_ddladmin'',
''db_denydatareader'',
''db_denydatawriter'',
''db_owner'',
''db_securityadmin'',
''INFORMATION_SCHEMA'',
''public'',
''PolicyAdministratorRole'',
''ServerGroupAdministratorRole'',
''ServerGroupReaderRole'',
''SQLAgentOperatorRole'',
''SQLAgentReaderRole'',
''SQLAgentUserRole'',
''TargetServersRole'',
''UtilityCMRReader'',
''UtilityIMRReader'',
''UtilityIMRWriter'',
''sys'',
''DatabaseMailUserRole'',
''db_ssisadmin'',
''db_ssisltduser'',
''db_ssisoperator'',
''dc_admin'',
''dc_operator'',
''dc_proxy'',
''MS_DataCollectorInternalUser'')
   /*and u.name like ''tester''*/ ORDER BY u.name
'


SELECT *
FROM #TUser
ORDER BY DBName,
 [Name]
 

DROP TABLE #TUser
END