Contained Database - No Need For Server Level Logins Anymore

SQL Server

Starting in SQL Server 2012 and in Azure SQL Database, Microsoft introduced the concept of a contained database. A contained database is a database that is completely un-reliant on the instance of SQL Server that hosts the database including the master database. Theoretically, this makes a database much easier to move between servers (You’ll note the absence of SQL Agent jobs from this post, that’s a different problem.). One of the biggest benefits is that it allows database level authentication, so there is no need to have user logins at Server level.

Contained database now enables us to make a database more portable. I can backup and restore to any instance of SQL Server and the database will carry all its logins with it. There is no longer a need to script out all logins and create those at the server instance level for a user to connect to that restored database. I personally have run into the issue of missing logins at instance level when restoring to another server. In these cases, I have had to go back and script out those logins to apply them to the new instance. You can see how in an emergency where the source server may not be available that not having access to those logins could present a real issue. This is also beneficial for databases that are members of Always On Availability Groups--you don’t have to create logins on each server.

In addition to portability, contained databases allow us to expand the control of login creation to more than just the database administrator or highly-privileged user accounts. Traditional databases require you to create server level roles and server level permissions in order to grant database rights to a user.  With contained databases, you avoid this, database owner and users with an ALTER ANY USER permission can now control access to the database. One drawback is the database user account must be independently created in each database that the user will need which adds a little more maintenance.

Below, I will show you how to enable this option at both the server and database levels. From there, I will show you how to create user logins and what the difference is between traditional (non-contained) login accounts and contained users.

Enable at Server level

Script

  1. EXEC sys.sp_configure N'contained database authentication', N'1'  
  2. GO  
  3. RECONFIGURE WITH OVERRIDE  
  4. GO  

GUI

SQL Server

Enable at database level

Note the word “Partial” in the dropdown and script.

PER MSDN

The contained database feature is currently available only in a partially contained state. A partially contained database is a contained database that allows the use of uncontained features.

Use the sys.dm_db_uncontained_entities and sys.sql_modules (Transact-SQL) view to return information about uncontained objects or features. By determining the containment status of the elements of your database, you can discover what objects or features must be replaced or altered to promote containment.

Script

  1. USE [master]  
  2. GO  
  3. ALTER DATABASE [AdventureWorks2016CTP3] SET CONTAINMENT = PARTIAL WITH NO_WAIT  
  4. GO  

GUI

SQL Server

To Add a User

Below, you will note a few differences in syntax. Traditionally we used the work LOGIN while contained uses USER. Also, note that when adding or changing database permissions, the ALTER statements are very different. Traditional uses ROLE and MEMBER while Contained uses AUTHORIZATION and SCHEMA.

Traditional NON-Contained, adding user and granting READ/WRITE to a database,

  1. CREATE LOGIN JoeShmo WITH PASSWORD = '1234Password';  
  2.   
  3. USE [AdventureWorks2016CTP3]  
  4. GO  
  5. CREATE USER [JoeShmo] FOR LOGIN [JoeShmo]  
  6. GO  
  7. USE [AdventureWorks2016CTP3]  
  8. GO  
  9. ALTER ROLE [db_datareader] ADD MEMBER [JoeShmo]  
  10. GO  
  11. USE [AdventureWorks2016CTP3]  
  12. GO  
  13. ALTER ROLE [db_datawriter] ADD MEMBER [JoeShmo]  
  14. GO  

Contained Database adding user and granting READ/WRITE to a database -- this works for both SQL Authentication and Windows.

  1. CREATE USER JoeShmo WITH PASSWORD = '1234strong_password';  
  2.   
  3. USE [AdventureWorks2016CTP3]  
  4. GO  
  5. ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [JoeShmo]  
  6. GO  
  7. USE [AdventureWorks2016CTP3]  
  8. GO  
  9. ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [JoeShmo]  
  10. GO  

If changing to Contained database and you want to convert all your Server Logins to contained database users, Microsoft has given us a great script to use. I have reposted it below. The example must be executed in the contained database.

  1. DECLARE @username sysname ;   
  2. DECLARE user_cursor CURSOR   
  3.     FOR    
  4.         SELECT dp.name    
  5.         FROM sys.database_principals AS dp   
  6.         JOIN sys.server_principals AS sp    
  7.         ON dp.sid = sp.sid   
  8.         WHERE dp.authentication_type = 1 AND sp.is_disabled = 0;   
  9. OPEN user_cursor   
  10. FETCH NEXT FROM user_cursor INTO @username   
  11.     WHILE @@FETCH_STATUS = 0   
  12.     BEGIN   
  13.         EXECUTE sp_migrate_user_to_contained    
  14.         @username = @username,   
  15.         @rename = N'keep_name',   
  16.         @disablelogin = N'disable_login';   
  17.     FETCH NEXT FROM user_cursor INTO @username   
  18.     END   
  19. CLOSE user_cursor ;   
  20. DEALLOCATE user_cursor ;  

Aside from the lack of support for MSDB, the one other issue I’ve run into with contained databases was an application that contained multiple databases supporting the applications but used SQL logins. In this case, it was a version of dynamics -- with Windows logins this is easy -- you simply create the login in each database and let Active Directory deal with the passwords. However, with contained databases the passwords are local to each database--so it’s a challenge to sync these accounts. With my current customer in this situation, we’ve reverted to server logins and used Dbatools to sync the passwords between servers.

I can think of many ways contained database can add benefits, I can’t wait to play around with it more.

Disclaimer

Contained databases do not support replication, change data capture, or change tracking. Confirm the database does not use these features.