Contained Database Authentication: Monitoring and controlling contained users

Published Mar 23 2019 11:44 AM 194 Views
Microsoft
First published on MSDN on Dec 03, 2010

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 .

Version history
Last update:
‎Mar 23 2019 11:44 AM
Updated by: