How to Check Database Access Permissions in SQL Server using HAS_DBACCESS

Summary: Quickly identify which databases on a SQL Server instance you have permission to connect to using the HAS_DBACCESS function.

Check Which Databases You Can Access in SQL Server

Ever wonder which databases you actually have permission to enter on a shared instance? Instead of clicking through every database in Object Explorer and waiting for "Access Denied" errors, you can run a simple script to audit your permissions at a glance.

Using the HAS_DBACCESS Function

The HAS_DBACCESS function returns a 1 if the user has access to the database, a 0 if the user does not have access, and NULL if the database name is invalid.

-- List all databases and check current user access
SELECT 
    name AS [Database Name], 
    CASE 
        WHEN HAS_DBACCESS(name) = 1 THEN 'Yes' 
        ELSE 'No' 
    END AS [Has Access]
FROM sys.databases
ORDER BY name;
        

Why Use This?

This statement is particularly handy for a quick security check. It helps you verify:

• If your login has been correctly mapped to a specific database.
• Which databases are visible but restricted to your current credentials.
• Connectivity status across a large instance with dozens of databases.

Working on security auditing? Combine this with our other scripts for a full view of your SQL Server environment!