How to Audit for SQL Server Users in 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.