Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to Audit for SQL Server Users in Contained Databases


By:   |   Last Updated: 2014-04-11   |   Comments   |   Related Tips: More > 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


Last Updated: 2014-04-11


next webcast button


next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools