How to Audit for SQL Server Users in Contained Databases

By: K. Brian Kelley   |   Updated: 2014-04-11   |   Comments   |   Related: > Contained Databases

Problem

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?

Solution

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;
GO
 
-- Required to use contained databases
EXEC sp_configure 'contained database authentication', 1;
GO 
RECONFIGURE;
GO 

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

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

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

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

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

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.

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:

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:

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.

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

SELECT ORIGINAL_LOGIN();
SELECT USER_NAME();

You should see ContainedUser as the result in both cases.

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):

SET NOCOUNT ON;
CREATE TABLE #ContainedDBUser (
  DBName sysname,
  UserName sysname
);

DECLARE cursContainedDBs CURSOR FAST_FORWARD
FOR SELECT name FROM sys.databases WHERE containment > 0;

DECLARE @DBName sysname;
DECLARE @SQL NVARCHAR(MAX);

OPEN cursContainedDBs;

FETCH NEXT FROM cursContainedDBs INTO @DBName;

WHILE (@@FETCH_STATUS = 0)

BEGIN
  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'');';
  EXEC(@SQL);
  FETCH NEXT FROM cursContainedDBs INTO @DBName;
END;

CLOSE cursContainedDBs;

DEALLOCATE cursContainedDBs;

SELECT DBName, UserName 
FROM #ContainedDBUser;

DROP TABLE #ContainedDBUser;

You should get a result back like this:

Next Steps

• See how to set up a contained database and user using the SSMS GUI instead of T-SQL.
• Learn how to use a contained database to move between environments.
• Read up on the connection between logins and users for normal databases.
• Know what to audit at the server level.

About the author

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