Contained Database Authentication: Monitoring and controlling contained users

Enabling contained database authentication on an instance allows db owners (and other privileged db users) to create and manage users who can connect to the database on the instance. However, the instance administrator (or other privileged server principal) may want to monitor database authentication – users and connections.

Here are some queries which should help monitor and control contained users from the instance level.

1. Detect that contained database authentication is enabled at the instance:

 

sp_configure 'show advanced', 1;

RECONFIGURE WITH OVERRIDE;

go

sp_configure 'contained database authentication';

go

2. List of contained databases on the instance:

SELECT database_id, name, containment_desc FROM sys.databases

WHERE containment > 0;

3. Users who can connect to the CDB. This includes all Windows users and groups, plus users with passwords in contained db (for example in db_Contained database):

SELECT principal_id, name, type_desc, authentication_type_desc

FROM db_Contained.sys.database_principals

WHERE authentication_type IN (2, 3);-- either user with password or Windows user\group

4. Current database authenticated sessions:

SELECT es.session_id, es.login_time, es.original_login_name, db.name AS 'CDb name'

FROM sys.dm_exec_sessions AS es JOIN sys.databases AS db

ON es.authenticating_database_id = db.database_id AND es.authenticating_database_id > 1;

Note, that Authenticating DatabaseId in the sys.dm_exec_sessions DMV is the Id of the database where the user was authenticated. For Server level authentication this is always master (Id = 1).

Read more about database authentication in further posts and in Books Online .