SQL Scripts: List Databases A Login Can Access

When you are managing your SQL Server database permissions through Active Directory Groups (which you should be!), it can sometimes be difficult to know just which databases a user actually has access to. Sure, you could look up each AD Group that user is a member of and then check the permissions for each group, but who has time for that? Below is a quick and dirty query that I wrote to tell me which databases a specified user can access.

Checking Your Access

By running this query, it will list every database on the server that YOU have access to.

The key to this query is the HAS_DBACCESS function. This function returns information about whether the user running the query has access to the specified database. Per the documentation, it behaves in the following ways:

HAS_DBACCESS returns 1 if the user has access to the database, 0 if the user has no access to the database, and NULL if the database name is not valid.
HAS_DBACCESS returns 0 if the database is offline or suspect.
HAS_DBACCESS returns 0 if the database is in single-user mode and the database is in use by another user.

Microsoft SQL Docs

It’s important to note that this query doesn’t tell you WHAT permissions a user has, it just returns a boolean “yes” or “no” value as to whether a user can access the specified database.

Checking A User’s Access

Now that we have a query that can check database access, we need to be able to run it for a different user. This can be done by specifying EXECUTE AS LOGIN just before the query. (also be sure to specify REVERT after the query runs)

Now our query will check the access of each database, running as the user specified in the EXECUTE AS statement, and only return a list of databases that they specified user does have access to. This will work for SQL Server Logins as well as Active Directory Logins. As mentioned before, this query only shows you what databases the user can access, not the permissions the user has on the databases.

Logins Vs. Users

If you look at the EXECUTE AS documentation, you will see that you have the option to run EXECUTE AS LOGIN or EXECUTE AS USER. The difference between these 2 can sometimes be a little confusing, so here is a simplified example that I found on the interwebs:

A “Login” grants the principal entry into the SERVER.

A “User” grants a login entry into a single DATABASE.

One “Login” can be associated with many users (one per database).

StackOverflow

What makes this tricky is that in the case of permissions being assigned to AD Groups, the specific user account you are testing will not have a SQL Server LOGIN or a Database USER.

So, how does this work?

LOGIN
Specifies the execution context to be impersonated is a login. The scope of impersonation is at the server level.

USER
Specifies the context to be impersonated is a user in the current database.

Microsoft SQL Docs

As I notated in the quote above, LOGIN impersonates a user at the server level, and USER checks for the user at the database level. So, when checking the permissions of a user’s account that is in an AD Group, you will need to use EXECUTE AS LOGIN instead of EXECUTE AS USER. As Microsoft states in the above listed documentation, the statement EXECUTE AS USER will fail because the user in the AD Group is not explicitly listed as a user in the database.

Yeah, it’s confusing. Just copy and paste the code above and see if it works. 😉