Contained Database - No Need For Server Level Logins Anymore

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

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

USE [master]  
GO  
ALTER DATABASE [AdventureWorks2016CTP3] SET CONTAINMENT = PARTIAL WITH NO_WAIT  
GO  

GUI

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 users and granting READ/WRITE to a database.

CREATE LOGIN JoeShmo WITH PASSWORD = '1234Password';  
USE [AdventureWorks2016CTP3]  
GO  
CREATE USER [JoeShmo] FOR LOGIN [JoeShmo]  
GO  
USE [AdventureWorks2016CTP3]  
GO  
ALTER ROLE [db_datareader] ADD MEMBER [JoeShmo]  
GO  
USE [AdventureWorks2016CTP3]  
GO  
ALTER ROLE [db_datawriter] ADD MEMBER [JoeShmo]  
GO  

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

CREATE USER JoeShmo WITH PASSWORD = '1234strong_password';  
USE [AdventureWorks2016CTP3]  
GO  
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [JoeShmo]  
GO  
USE [AdventureWorks2016CTP3]  
GO  
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [JoeShmo]  
GO  

If changing to a 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.

DECLARE @username sysname ;   
DECLARE user_cursor CURSOR   
    FOR    
        SELECT dp.name    
        FROM sys.database_principals AS dp   
        JOIN sys.server_principals AS sp    
        ON dp.sid = sp.sid   
        WHERE dp.authentication_type = 1 AND sp.is_disabled = 0;   
OPEN user_cursor   
FETCH NEXT FROM user_cursor INTO @username   
    WHILE @@FETCH_STATUS = 0   
    BEGIN   
        EXECUTE sp_migrate_user_to_contained    
        @username = @username,   
        @rename = N'keep_name',   
        @disablelogin = N'disable_login';   
    FETCH NEXT FROM user_cursor INTO @username   
    END   
CLOSE user_cursor ;   
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.