Retrieving Database Users in MS SQL Server: Including Windows and SQL Users Excluding 'dbo'

Introduction

In managing databases on Microsoft SQL Server, administrators often need to retrieve a comprehensive list of all users who have access to a specific database. This includes both SQL Server users and Windows-authenticated users, while typically excluding the default owner user dbo. The task involves identifying the actual user who owns the database in place of dbo and ensuring that the list resembles what one would see in SQL Server Management Studio (SSMS).

This tutorial will guide you through various methods to retrieve such a list using T-SQL queries. We’ll explore how to differentiate between different types of users, identify database owners, and exclude certain default entries like dbo.

Understanding Database Principals

Database principals are the entities that can authenticate with the database. They include:

• SQL Users: Created within SQL Server for authenticating.
• Windows Users/Groups: Windows accounts that can access the database using their credentials.
• Roles: Groups of users that have similar permissions.

In SQL Server, these principals are stored in system catalog views such as sys.database_principals and sys.server_principals.

Retrieving All Database Users

To get a list of all database users, including Windows users but excluding the default owner (dbo), you can use the following query:

SELECT 
    dp.name AS UserName,
    dp.type_desc AS UserType,
    suser_sname(dp.sid) AS OwnerName
FROM 
    sys.database_principals AS dp
WHERE 
    (dp.type IN ('S', 'U', 'G')) -- Windows users, SQL users, and database roles
    AND dp.name <> 'dbo'        -- Exclude the default owner
ORDER BY 
    UserName;

Explanation

• sys.database_principals: This view contains a row for each security principal that has access to the current database. It includes information about both Windows and SQL Server users.

• dp.type IN (‘S’, ‘U’, ‘G’): Filters principals to include only:

  • S: Windows user or group
  • U: SQL user
  • G: Database role

• suser_sname(dp.sid): This function translates a security identifier (SID) to the name of the login. It’s used here to identify the actual owner of the database in cases where dbo is mapped to another user like ‘sa’.

Identifying the Owner of a Database

To find out who owns a particular database, you can query:

SELECT 
    suser_sname(owner_sid) AS OwnerName,
    state_desc,
    name AS DatabaseName
FROM 
    sys.databases;

Explanation

• sys.databases: Contains a row for each database on the SQL Server instance.

• owner_sid: A column in sys.databases that contains the SID of the user who owns the database.

Retrieving All Server-Level Users

If you need to list all users at the server level, including both SQL and Windows users, use:

SELECT 
    sp.name AS UserName,
    sp.type_desc AS UserType
FROM 
    sys.server_principals AS sp;

Explanation

• sys.server_principals: This view contains a row for each login that has been defined at the server level. It’s useful for listing all potential users who can access any database on the instance.

Best Practices and Additional Tips

1. Regular Audits: Regularly audit your user lists to ensure compliance with security policies.
2. Least Privilege Principle: Assign only necessary permissions to each user or role to minimize security risks.
3. Documentation: Document the purpose of each user or group in your database for easier management and troubleshooting.

By following these methods, you can efficiently manage and audit access to your databases on SQL Server, ensuring that the users list is both comprehensive and secure.