How to Audit for SQL Server Users in Contained Databases

By:   |   Comments   |   Related: > Contained Databases


I know SQL Server 2012 added a feature where you can use contained databases. I also have heard that these databases can have users which can be authenticated by the SQL Server. However, the users don't appear in the standard sys.sql_logins or sys.server_principals catalog views. How can I audit for these users?


SQL Server 2012 does introduce contained databases with users which can be authenticated, users that don't have to be created and granted access at the server level. In order to audit for such users, let's understand how all this works. First, let's setup a test case:

USE master;
-- Required to use contained databases
EXEC sp_configure 'contained database authentication', 1;

-- Create a contained database
IF EXISTS(SELECT name FROM sys.databases WHERE name = 'TestContain')
  DROP DATABASE TestContain;

-- See the contained database in sys.databases
SELECT name, containment, containment_desc
FROM sys.databases
WHERE containment <> 0;

-- Create a user in the contained database
USE TestContain;
CREATE USER ContainedUser WITH PASSWORD = 'S0meStr0ngP4ssw0rd!';

-- Verify the login does not exist
SELECT name 
FROM master.sys.server_principals 
WHERE name = 'ContainedUser';

-- Verify the user does
SELECT name 
FROM TestContain.sys.database_principals
WHERE name = 'ContainedUser';

First, take note of the query that hits against sys.databases. If we look at the containment column, we can find which databases are contained databases. Don't be alarmed at the description of partial. SQL Server 2012 only supports partially contained databases. You can read more about what that means in Books Online. For audit purposes, whether the database was partial or full doesn't matter to us.

kbk Audit Contained DB 01

Take note of the two tests at the end of the script. The first verifies that ContainedUser is not present at the server level and the second shows that it exists within the database. With our setup in place, let's test how the user works. Here are the results of both tests:

kbk Audit Contained DB 02

Attempt to connect to the SQL Server where you created the database using a Database Engine Query and specify the ContainedUser username and password (File | New | Database Engine Query). You should get an error like this:

kbk Audit Contained DB 03

The reason this connection fails is the database isn't specified. Therefore, SQL Server is looking at the server level and in master. Since the user in question doesn't exist in those places, the connection fails. Click on the Options button and specify the database using Connection Properties.

kbk Audit Contained DB 04

And as you'll see, the user is able to connect. To verify that it's the ContainedUser, run the following queries:


You should see ContainedUser as the result in both cases.

kbk Audit Contained DB 05

Auditing Contained Users

Now that we know the contained database user is found and authenticated, let's talk about how to audit for it.

  1. We have to determine what databases are setup as contained databases.
  2. Then we simply have to query the sys.database_principals catalog view in each of those databases.

This is easy to do. There are a couple of caveats, though:

  • We want to make sure we only return SQL Server users, Windows users, and Windows groups. Therefore, we'll need to filter on type.
  • We want to exclude dbo, guest, sys, and INFORMATION_SCHEMA, which appear as SQL Server users.

With all that in mind, here's the query to audit for users in contained databases (if you're following along, make sure you run this with privilege rights, not in the Query window for ContainedUser):

CREATE TABLE #ContainedDBUser (
  DBName sysname,
  UserName sysname

FOR SELECT name FROM sys.databases WHERE containment > 0;

DECLARE @DBName sysname;

OPEN cursContainedDBs;

FETCH NEXT FROM cursContainedDBs INTO @DBName;


  SET @SQL = 'INSERT INTO #ContainedDBUser (DBName, UserName)
  SELECT ''' + @DBName + ''', name 
  FROM [' + @DBName + '].sys.database_principals
  WHERE type IN (''U'', ''S'', ''G'')
    AND name NOT IN (''dbo'', ''guest'', 
                  ''INFORMATION_SCHEMA'', ''sys'');';
  FETCH NEXT FROM cursContainedDBs INTO @DBName;

CLOSE cursContainedDBs;

DEALLOCATE cursContainedDBs;

SELECT DBName, UserName 
FROM #ContainedDBUser;

DROP TABLE #ContainedDBUser;

You should get a result back like this:

kbk Audit Contained DB 06
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms