Who has access?

A common task for a DBA is to find out who has access (and what access) to a SQL Server or a database. Another common task is to find out, what SQL Servers and databases a given user has access to.

These can be more complicated tasks, if a user is a member of an AD group, and the user is only given access through AD group memberships. It complicates it further if the user is a member of an AD group that is a member of an AD group etc. What if the user is disabled on the SQL Server or the account has expired in AD? What if the user exists in the database but not on the SQL Server?

Performance Store gives direct and immediate access to this information from the following views:

v_server_security

Will show information about all SQL logins, Windows logins and AD groups on the SQL Servers:

It also shows information on when the user has last logged in and if the AD account is disabled:

The view also contains the statement for creating the login including all grants etc. This is extremely useful in a disaster scenario, where databases from a backup needs to be restored on a new SQL Server and the corresponding server-level logins needs to be created exactly as on the old SQL Server (which might not be accessible):

v_database_security

Like v_server_security, the v_database_security view shows information about all SQL logins, Windows logins and AD groups in databases:

From this view, it is easy to quickly see e.g. who is db_owner in what databases or if any user is having an incorrect default schema.

v_server_access and v_database_access

The v_server_security and v_database_security views shows what SQL logins, Windows logins and AD groups that are in the SQL Servers and databases.

When querying v_server_access and v_database_access, all AD users in all AD groups will be shown, including users in nested AD groups. This makes it quick and easy to see where exactly users have access without the information being obfuscated by nested AD group memberships.

The connection between users and AD groups can be seen in the ad_group_members table. Note, only AD groups that are used on SQL Servers are included. E.g. if an AD group is not created on any SQL Server, the AD group will not be included in the ad_group_members table.

Example: To see all users in a database:

select distinct a.server_name, a.database_name, a.member_name, a.member_sid
from dbo.v_database_access a
where a.is_disabled = 0 and a.ad_enabled = 1
and a.member_type = 'U'
and a.database_name = 'testdb1'

To see where the users get their access from (and what access):

select *
from dbo.v_database_access a
where a.is_disabled = 0 and a.ad_enabled = 1 and a.exists_on_server = 1
and a.database_name = 'testdb1'

Note, users might have access to the database from a server-level permission. To see server-level users:

select distinct a.server_name, a.member_name, a.member_sid
from dbo.v_server_access a
where a.member_type = 'U'
and a.server_name = 'LHG_SQL03\SS01'

To see where the users get their server-level access from (and what access):

select *
from dbo.v_server_access a
where a.server_name = 'LHG_SQL03\SS01'

Read more about the access information in the dashboards: Database access dashboard and Server access dashboard.