How To Check Database Role Permissions In Sql Server

Understanding database role permissions in SQL Server is your first line of defense. In this guide, I’ll show you how to pull back the curtain on SQL Server security. We’ll cover everything from the GUI approach to the deep-level system views that professional DBAs and developers use.

How To Check Database Role Permissions In Sql Server

The Layers of SQL Server Security

Before we dive into the “how,” we must understand the “where.” SQL Server handles security in two distinct scopes:

1. Server-Level: Handled by Logins and Server Roles (e.g., sysadmin, securityadmin).
2. Database-Level: Handled by Users and Database Roles (e.g., db_owner, db_datareader).

Today, we are focusing on Database Roles. These are the containers within a specific database that hold permissions for tables, views, and stored procedures.

Method 1: The Visual Approach (SSMS GUI)

If you’re a visual learner or just need a quick check on a single user in a small office in Seattle, the SQL Server Management Studio (SSMS) interface is your best friend.

Steps to View Role Membership

1. Open SSMS and connect to your instance.
2. Expand the Databases folder.
3. Select your target database (e.g., SalesData_Prod).
4. Expand Security and then Users.
5. Right-click a specific user and select Properties.
6. Click the Membership page. Here, you will see a list of all database roles. The checked boxes indicate which roles the user currently holds. Check out the screenshot below for your reference.

Steps to View Role Permissions

If you want to see what the role itself is allowed to do:

1. Under the same Security folder, expand Roles > Database Roles.
2. Right-click a role (like db_datareader or a custom role like Marketing_Analyst) and select Properties.
3. Click the Securables page.
4. Click the Search button to add objects if the list is empty, or view the “Effective” tab to see the final resulting permissions. Check out the screenshot below for your reference.

Method 2: The Scripting Powerhouse (T-SQL)

The GUI is great for one-offs, but it’s inefficient for auditing 50 databases. For that, we use T-SQL. As an admin, I prefer scripts because they are repeatable, documentable, and fast.

Identifying Role Members

The following query is my “go-to” for identifying which users belong to which roles. We use three key system views: sys.database_role_members, sys.database_principals (for the roles), and sys.database_principals (again, for the members).

SQL

SELECT 
    DP1.name AS DatabaseRoleName, 
    ISNULL(DP2.name, 'No members') AS DatabaseUserName 
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1 
    ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2 
    ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R' -- Only return Roles
ORDER BY DP1.name;

After executing the above query, I got the expected output as shown in the below screenshot.

Checking Explicit Permissions for a Role

If you have a custom role and need to know exactly which tables it can SELECT from or UPDATE, you need to query sys.database_permissions.

SQL

SELECT 
    pr.name AS RoleName, 
    pe.permission_name AS Permission, 
    pe.state_desc AS PermissionState, 
    OBJECT_NAME(major_id) AS ObjectName,
    obj.type_desc AS ObjectType
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe 
    ON pe.grantee_principal_id = pr.principal_id
LEFT JOIN sys.objects AS obj 
    ON pe.major_id = obj.object_id
WHERE pr.type = 'R' -- Filter for Roles
AND pr.name = 'YourCustomRoleName'; -- Replace with your role

Understanding Fixed Database Roles

SQL Server comes pre-loaded with “Fixed Database Roles.” You cannot change their permissions, but you can add users to them. Understanding these is vital.

Advanced Technique: Checking “Effective” Permissions

Sometimes, a user’s permissions are a messy “soup” of multiple roles. They might be in a role that grants SELECT but also in a Windows Group that is specifically denied SELECT.

In these cases, I use the sys.fn_my_permissions function. This shows the Effective Permissions—the final result after SQL Server calculates all the Grants and Denies.

To check the effective permissions of the current user on the database:

SQL

SELECT * FROM fn_my_permissions(NULL, 'DATABASE');

To check permissions on a specific table:

SQL

SELECT * FROM fn_my_permissions('Sales.Orders', 'OBJECT');

Best Practices for Managing Roles

Below are the set of “Golden Rules” for database role management:

• Principle of Least Privilege: Never make someone a db_owner just because they asked. Start with db_datareader and add only what is necessary.
• Use Groups, Not Individuals: In a US corporate environment, always map Active Directory (AD) Groups to SQL Server Roles. If a user joins the accounting team, you simply add her to the AD Group. You shouldn’t have to touch SQL Server.
• Avoid Custom Roles Where Possible: SQL Server’s fixed roles are robust. Only create a custom role if the built-in ones are too broad.
• Regular Audits: Run the scripts I provided at least once a quarter. People move departments, and “permission creep” is a real security risk.

Troubleshooting Common Issues

Why can’t I see the permissions?

If you run these queries and get an empty result set, you likely lack the VIEW DEFINITION permission. In SQL Server, you can’t see the metadata of objects you don’t have permission to see. Ask your Lead DBA to grant you VIEW ANY DEFINITION at the server level if you are in an auditing role.

Why is a user still able to delete data if they aren’t in db_datawriter?

Check for Ownership Chaining. If a user has permission to execute a stored procedure, and that procedure deletes data from a table owned by the same user (usually dbo), SQL Server doesn’t check the table permissions. This is a common “gotcha” in legacy applications.

Summary and Key Takeaways

Checking database role permissions is a mix of utilizing the SSMS interface for quick checks and mastering T-SQL for deep-dive audits.

• Use sys.database_role_members to find out who is in what role.
• Use sys.database_permissions to see exactly what those roles are allowed to touch.
• Use fn_my_permissions to see the final, effective truth of a user’s access.

By following these steps, you’ll ensure your SQL Server environment remains secure, compliant, and well-documented.

You may also like the following articles:

• How to Check Object Level Permissions in SQL Server
• SQL Server Permissions
• SQL Server Grant Select On Table
• SQL Server Check User Permissions On Table

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.