An access audit needs more than the number shown in a properties dialog. Counting users across databases starts with a consistent definition. Include authentication type, role membership, and schema ownership so the count has a useful meaning.
Define the Scope Before Counting Users
Database principals include users, roles, system identities, and special-purpose accounts. A raw count of sys.database_principals mixes those categories. Decide which user types belong in the audit and keep roles separate.
The example includes application-facing user types and excludes built-in principal identifiers. That is an inventory definition, not a statement that excluded identities have no security significance.
I put the definition beside the report before collecting numbers. SQL users, Windows users, groups, and external identities carry different authentication semantics. A group represents one principal with several possible members.
Counting it once doesn't count every person who can access the database. State that limitation plainly. Counting users records configured identities and relationships, not a complete personnel roster.
Walk Accessible Databases With a Cursor
Use a documented cursor over sys.databases instead of the undocumented foreach helper. Filter online databases that the audit connection can access. Include system databases deliberately if they are part of the review.
QUOTENAME protects the database identifier in dynamic SQL. It doesn't grant access or reveal metadata hidden from the caller. The account needs appropriate visibility for a complete report.
The first query shows the candidate databases. Keep its output with the audit coverage. A skipped inaccessible database must remain visible as an audit gap, rather than disappearing from a success count.
Compare the candidate list against the intended scope. A readable report from three databases isn't a complete server review when the fourth was quietly excluded by permissions.
SELECT name,state_desc,HAS_DBACCESS(name) AS AuditAccess
FROM sys.databases
ORDER BY database_id;Capture One Record per User
The collection stores the database, identity type, authentication type, role names, schema names, and orphan flag. A user with several roles remains one user record. STRING_AGG gathers related names without multiplying the user count.
Use a separate role-level export when the auditor needs each membership edge individually. The one-row-per-user result is a practical overview, not a replacement for every detailed permission review.
The dynamic statement runs in each database context. Login matching uses SID, not the display name. A renamed user can still map correctly. Conversely, identical names can have different SIDs.
The orphan check applies to instance-authenticated SQL users only. Contained users and Windows group behavior need their own interpretation. Avoid declaring every principal without a visible login to be broken.
CREATE TABLE #UserAudit
(
DatabaseName sysname,PrincipalName sysname,PrincipalType nvarchar(60),
AuthenticationType nvarchar(60),RoleNames nvarchar(max),OwnedSchemas nvarchar(max),
IsOrphan bit
);
DECLARE @Database sysname,@Sql nvarchar(max);
DECLARE AuditDatabases CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM sys.databases WHERE state = 0 AND HAS_DBACCESS(name) = 1;
OPEN AuditDatabases;
FETCH NEXT FROM AuditDatabases INTO @Database;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sql = N'USE ' + QUOTENAME(@Database) + N';
INSERT #UserAudit
SELECT DB_NAME(),p.name,p.type_desc,p.authentication_type_desc,
(SELECT STRING_AGG(CONVERT(nvarchar(max),r.name),N'', '')
FROM sys.database_role_members AS rm
JOIN sys.database_principals AS r ON r.principal_id = rm.role_principal_id
WHERE rm.member_principal_id = p.principal_id),
(SELECT STRING_AGG(CONVERT(nvarchar(max),s.name),N'', '')
FROM sys.schemas AS s WHERE s.principal_id = p.principal_id),
CONVERT(bit,CASE WHEN p.type = ''S'' AND p.authentication_type = 1
AND sp.sid IS NULL THEN 1 ELSE 0 END)
FROM sys.database_principals AS p
LEFT JOIN sys.server_principals AS sp ON sp.sid = p.sid
WHERE p.principal_id > 4 AND p.type IN (''S'',''U'',''G'',''E'',''X'');';
EXEC sys.sp_executesql @Sql;
FETCH NEXT FROM AuditDatabases INTO @Database;
END;
CLOSE AuditDatabases;
DEALLOCATE AuditDatabases;
Interpret the Orphan Flag Carefully
A restored SQL user can retain a SID that no target login matches. That is a common orphaned-user case. The report flags it for review.
Missing metadata visibility can also conceal a login from the audit account. Confirm server-level permissions before treating the flag as a finding. The query doesn't repair anything, and a name match alone isn't enough evidence for remapping.
I verify the application owner and intended login before using ALTER USER elsewhere in a repair task. An obsolete user and a required orphan need different actions. This inventory should preserve that distinction.
A login created with the wrong SID can restore the familiar name while leaving the mapping broken. Keep the original SID and the approved target identity with any later change plan.
Keep Roles and Schemas in View
Role membership gives a useful starting map but doesn't list all effective permissions. Users can have direct grants and denials. Roles can belong to other roles.
Ownership can also change permission behavior. Review those paths for a full access decision. A user with no listed application role isn't necessarily harmless, especially when that user owns a schema containing important objects.
Schema ownership matters before removing a principal. A drop can fail while owned objects remain, or a reassignment can change the security boundary. Inventory ownership before deciding cleanup.
Which user would leave an ownership dependency behind if removed today? The report makes that question visible. The count alone was never going to answer it, however neatly the total appeared in the audit spreadsheet.
Counting Users Without Multiplying Memberships
The final query adds a count per database and principal type using a window aggregate. It operates over one row per collected user, so several roles don't inflate the count. Keep the type beside the number.
A Windows group and a SQL user aren't interchangeable units. For the total principal count, aggregate the same collected rows under the same declared scope.
Save the result with the collection time and the audit account's coverage. Database creation or permission changes during collection can alter what is accessible. A periodic inventory benefits from a stable schema and comparison method.
Don't claim last-login activity from create_date or modify_date. Those columns describe metadata changes, not whether a user still serves an application.
Finish Counting Users in One Cross-Database Result
Use the final table to review identity types, orphan candidates, roles, and ownership together. Follow flagged rows with the detailed permission and application checks they need. Keep inaccessible databases in the coverage notes.
Counting users becomes useful when it answers what was counted and what remains unknown. A tidy total without that context offers confidence the query hasn't earned.
The result below is the single combined report for accessible databases. Export it locally under the audit's access controls. It contains account names and security relationships, so share it only within the approved review.
The data remains unchanged. Review each finding before deciding on cleanup. An unfamiliar name doesn't establish that the principal is unnecessary.
SELECT DatabaseName,PrincipalType,
COUNT_BIG(*) OVER(PARTITION BY DatabaseName,PrincipalType) AS UsersOfType,
PrincipalName,AuthenticationType,RoleNames,OwnedSchemas,IsOrphan
FROM #UserAudit
ORDER BY DatabaseName,PrincipalType,PrincipalName;Roles themselves deserve a separate inventory, including nested membership and owners. Keep the database role definition with the user report so an empty role isn't silently omitted. User counts explain configured identities. Role definitions explain the permission containers into which those identities can be placed during the next authorized change.
Related reading on this blog: Running a Command in Every Database Without sp_MSforeachdb and Orphaned Users After a Restore, and How to Fix Them.
An access count is not an access review, it is an inventory with a declared scope.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
