When it comes to implementing contained databases in SQL Server, there are certain scenarios where you may want to restrict the creation and usage of these databases. In this article, we will explore a method to achieve this using logon triggers.

Recently, I had an interesting conversation with a DBA friend who wanted to ensure that only authorized users and databases could act as contained databases on their server. They wanted to track every login that was authenticated inside SQL Server and implement a different process for auditing and security purposes.

To restrict the creation and usage of contained databases, we can follow these steps:

1. Create a table in the master database to hold the list of authorized databases.
2. Insert the database IDs of the authorized databases into the created table.
3. Create a logon trigger to restrict the login process.
4. Test the logon process with both a normal user and a contained database user.
5. Cleanup the script after testing.

Let’s walk through the implementation:

-- Step 1 - Create a table in Master to hold databases that can be used for authentication.
USE MASTER
GO
CREATE TABLE [dbo].[authenticated_DBs] (dbs INT PRIMARY KEY);
GO

-- We want anyone to be able to access this data for read-only purposes
GRANT SELECT ON [dbo].[authenticated_DBs] TO PUBLIC;
GO

The first step is to create a table in the master database to store the database IDs of the authorized databases. We have chosen the master database because logon triggers are server-scoped objects.

-- Step 2 - Insert the list of databases into the created table.
-- Add the DB ID for all of the DBs authorized to authenticate, including/excluding the master DB
INSERT INTO [dbo].[authenticated_DBs] VALUES (DB_ID('master'));
GO

-- Insert the database ID for the ContainedDatabase
INSERT INTO [dbo].[authenticated_DBs] VALUES (DB_ID('ContainedDatabase'));
GO

In the next step, we insert the database IDs of the authorized databases into the table. In this example, we have included the “master” database and a database called “ContainedDatabase” based on a previous blog post.

-- Step 3 - Create the Logon Trigger to restrict the login available
-- This logon trigger will verify the current logon matches with one of the authorized DBs.
-- If it does, it allows the logon process to continue,
-- otherwise, it will rollback, causing the session to terminate
CREATE TRIGGER Logon_authenticated_dbs ON ALL SERVER FOR LOGON AS
BEGIN
  IF NOT (EXISTS(SELECT * FROM [dbo].[authenticated_DBs] WHERE dbs IN (SELECT authenticating_database_id FROM sys.dm_exec_sessions WHERE session_id = @@spid)))
    ROLLBACK;
END;
GO

The logon trigger is the crucial part of this implementation. It verifies if the current logon matches with one of the authorized databases. If it does, the logon process continues; otherwise, it rolls back and terminates the session.

After implementing the logon trigger, you can test the login process with both a normal user and a contained database user. If the user is authorized and the database is listed in the table, the login process will succeed. Otherwise, an error message will be displayed.

-- Step 5 - Delete an entry from our table and check by logging in.
-- Your [ContainedDatabase] database ID might not be 7. Change appropriately
DELETE [dbo].[authenticated_DBs] WHERE dbs = 7

-- Try to connect again using ContainedUser
-- ContainedUser fails to connect due to the trigger
INSERT INTO [dbo].[authenticated_DBs] VALUES (DB_ID('ContainedDatabase'));
GO

-- Try to connect again as ContainedUser. Now it should work.

Finally, don’t forget to clean up the script after testing:

USE MASTER
GO

-- CAUTION: If you forget to do this and drop the table authenticated_DBs, then you need to manually delete this trigger, or else you can't get into master too
DROP TRIGGER Logon_authenticated_dbs ON ALL SERVER
GO

DROP TABLE authenticated_DBs
GO

It’s important to note that logon triggers can be powerful tools, but they should be used with caution. A poorly written trigger can potentially lock you out of your SQL Server instance. Always test and verify your scripts before implementing them in a production environment.

By following these steps, you can effectively restrict the creation and usage of contained databases in SQL Server, ensuring that only authorized users and databases can utilize this capability.