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

Question

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

techresearch7777777 1,511

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,511 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,511

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

techresearch7777777 1,511 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,511 Reputation points

2023-05-18T00:48:29.92+00:00

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

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

Activity