SQL Server – Contained Database

Introduction

You may have encounter already case when an application offers the possibility to create and maintain SQL database users and consequently the associated SQL Server instance logins. That’s of course not especially really what a DBA likes, loosing control on the logins that are created on the instance he is responsible of. But sometimes you have to deal with such situation especially when a customer ask you to enable this.
Well, then it’s starts to be more complicated when the application of that database is part of an always on availability group. You can imagine what happen in case of failover, all the logins created within the application will not be present on the secondary replicas and all connections to the new primary replica will fail.

We had a sync process in place to make sure that the logins were available in all replicas. That’s a good solution, but the users could also change their password through the application. In such case the process to sync the logins becomes more complicated. Thus before starting a long development process, I wanted to experiment and test the containment option that presumably could be a good option in such case.

In fact, experimenting how containment option is working, the creation of the database users must be done differently and therefore if your application is not aware or fitting with this option it won’t solve your problem.

Set the Containment option for your database

The first thing you have to do is to configure your SQL Server instance to enable the containment authentication.

For that you have to change the “contained database authentication” configuration option.

--###Scripts:
---------------------------------------------------------------------
execute sp_configure 'contained database authentication',1
Reconfigure
---------------------------------------------------------------------

Caution: if you are in an always on availability group situation, of course do not forget to do the same on all the replicas otherwise you will face some problems after a primary switch.

Then you have to change your data base to enable this option.

--###Script:
---------------------------------------------------------------------
USE [master]
GO
ALTER DATABASE [DB_Contained] SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO
---------------------------------------------------------------------

You can do the same using your database properties GUI in SSMS.

Create the user on your database level

Now that the option is enable, if you want to create a new database user, you will see that new options appeared “SQL User with password”

You can create you database user using the following script

--###Script:
-----------------------------------------------------------
USE [DB_Contained]
GO
CREATE USER [user_with_pwd] WITH PASSWORD=N'**********'
GO
-----------------------------------------------------------

You can also create a domain account

--###Script:
-----------------------------------------------------------
USE [DB_Contained]
GO
CREATE USER [YourDomain\YourAccount] WITHOUT LOGIN
GO
-----------------------------------------------------------

Be aware that in this case the database user will be shown with an associated login but it will not be present at instance level and you will not see it listed in the server logins.

Connect to you database

Remember that with the contained database the login will be done on database level therefore you must specify the catalog (the database) to which you want to establish the connection otherwise the connection will failed.

Conclusion

This could be a nice solution to have a portable database and make it independent from the instance it is located on, and therefore without having to worry about to copy the logins when it is moved to another server.
But of course it must fit your case.