Comprehensive Guide to Querying Database Users in SQL Server: Best Practices and Deep Analysis

Introduction

In SQL Server database management and security auditing, accurately retrieving database user lists is a fundamental yet crucial task. Many developers and database administrators need to obtain the same user lists displayed in SQL Server Management Studio (SSMS), but with specific requirements such as properly handling dbo user display issues.

Problem Background and Requirements Analysis

In practical applications, users often need complete lists of all users in specific databases, including both Windows users and SQL users. Key requirements include: displaying actual database owners instead of simple 'dbo' identifiers, including all types of user principals, and ensuring results match the SSMS interface display.

Basic Query Methods and Their Limitations

Initial query attempts typically rely on the sys.database_principals system view:

SELECT * FROM sys.database_principals where (type='S' or type = 'U')

This query can retrieve most SQL users (type='S') and Windows users (type='U'), but has significant limitations: it cannot properly handle dbo user display. In SQL Server, dbo is an alias for the database owner, and when actual owners (such as 'sa') need to be displayed, this query fails to meet the requirement.

SSMS Internal Query Mechanism Analysis

By capturing queries executed by SSMS when refreshing user lists using SQL Profiler, we obtain complete solutions written by Microsoft experts:

SELECT
u.name AS [Name],
'Server[@Name=' + quotename(CAST(
        serverproperty(N'Servername')
       AS sysname),'''') + ']' + '/Database[@Name=' + quotename(db_name(),'''') + ']' + '/User[@Name=' + quotename(u.name,'''') + ']' AS [Urn],
u.create_date AS [CreateDate],
u.principal_id AS [ID],
CAST(CASE dp.state WHEN N'G' THEN 1 WHEN 'W' THEN 1 ELSE 0 END AS bit) AS [HasDBAccess]
FROM
sys.database_principals AS u
LEFT OUTER JOIN sys.database_permissions AS dp ON dp.grantee_principal_id = u.principal_id and dp.type = 'CO'
WHERE
(u.type in ('U', 'S', 'G', 'C', 'K' ,'E', 'X'))
ORDER BY
[Name] ASC

Query Deep Analysis

The core advantage of this query lies in its comprehensive user type coverage and proper permission handling:

• User Type Filtering: The WHERE clause u.type in ('U', 'S', 'G', 'C', 'K', 'E', 'X') ensures all relevant user types are included:
• 'U': Windows users
• 'S': SQL users
• 'G': Windows groups
• 'C': Users mapped to certificates
• 'K': Users mapped to asymmetric keys
• 'E': External users
• 'X': External groups
• Permission Association: Through LEFT OUTER JOIN with sys.database_permissions, it identifies whether users have database access permissions
• URN Construction: Complete URN strings provide standard object identifiers

Database Owner Handling Solution

For special handling of dbo users, database-level queries are required:

select suser_sname(owner_sid) as 'Owner', state_desc, *
from sys.databases
where name = 'YourDatabaseName'

This query retrieves actual database owner information, which can be associated with user lists when dbo display replacement is needed.

Server-Level User Queries

For scenarios requiring viewing all principals at server level:

select * from master.sys.server_principals

This query returns all security principals at server level, including login names and server roles.

Alternative Solutions Comparison

Beyond the above methods, other alternatives exist:

• sp_helpuser Stored Procedure: Provides user information for current database, but with fixed format
• sysusers System View: Older compatibility view, recommending use of newer sys.database_principals

Cross-Database User Queries

In certain scenarios, complete user lists across all databases are needed, requiring dynamic SQL or system stored procedures:

EXEC sp_MSForEachdb '
SELECT 
''?'' as DatabaseName,
u.name as UserName,
u.type as UserType
FROM [?].sys.database_principals u
WHERE u.type IN (''U'', ''S'', ''G'', ''C'', ''K'', ''E'', ''X'')
AND u.name NOT LIKE ''##%''
'

Security Considerations and Best Practices

When executing user queries, consider the following security factors:

• Ensure executing users have appropriate permissions
• Test all queries in production-like environments
• Avoid directly using code from untrusted sources
• Regularly audit user permissions and access patterns

Performance Optimization Recommendations

For large database environments, user queries may impact performance:

• Execute user audit queries during off-peak hours
• Consider using filter conditions to reduce returned data volume
• For frequently executed queries, consider creating appropriate indexes

Conclusion

Through in-depth analysis of SQL Server's user management mechanisms, we provide complete solutions for obtaining accurate database user lists. The method based on SSMS internal queries is the most reliable choice, properly handling all user types and permission associations while providing results consistent with the graphical interface. Combined with database owner queries, it fully meets the requirement of displaying actual database owners instead of simple dbo identifiers.