SQL SERVER – Beginning Contained Databases – Notes from the Field #037

[Note from Pinal]: This is a new episode of Notes from the Fields series. Login and Users are very confused concept. Just yesterday I wrote about a difference between Login and User. In Latest version of SQL Server now we can also have a User without Login. This concept is not easy to understand and needs a clear example.

In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very common issue DBAs and Developer faces related to Login and Users. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.

One of the downfalls with database mirroring is synchronizing database users with logins. If you were using SQL Server accounts instead of windows domain accounts it could be a tougher challenge to ensure that the unique identifiers (SID) match up. In SQL Server 2012 and up we can leverage contained databases to mitigate this problem with Availability Groups. Contained database users are stored inside the database instead of leveraging the instance logins. This allows the database itself to manage authentication and authorization for the login for the database.  Therefore, you don’t need to fixed orphan users when you failover.

Here is how you can enable and leverage Contained Databases.

Step One: Enable Contained Database on the instances that will host the database. In an Availability Group this would be all the replicas for the Availability Group.

This can be configured in SSMS or via T-SQL as shown below.

EXEC sp_CONFIGURE 'show advanced options',1
GO
RECONFIGURE
GO
EXEC sp_CONFIGURE 'contained database authentication',1
GO
RECONFIGURE
GO

Step Two: Enable Contained Database on the primary replica. This is also known as the read/write replica.

This can be done via SSMS or T-SQL

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

Step Three: Create a Contained User
In order to have a contained user were going to have to create a new user inside the database and create it without a login. This will make the user a contained user. You will also need to add the needed security for your account. For the purpose of this weekly tip we will skip this part. Creating the contain login also known as SQL user without login can be done with SSMS or T-SQL as shown below.

USE [AdventureWorks2012]
GO
CREATE USER [MyContainedUser] WITH PASSWORD=N'!LPPeople!', DEFAULT_SCHEMA=[dbo]
GO

Step Four: Test connectivity.

Finally, we can test connectivity. This will be done utilizing SSMS as shown below.  Make sure you change the default database to the database where the login is contained as shown below.

Now, make sure you select the Connection Properties tab and select the database where you created the contained login. In our example, this will be AdventureWorks2012.

When connection is successful with a contained database user you should see the login and the database right next to the instance in object explorer as shown below.

Are your servers running at optimal speed or are you facing any SQL Server Performance Problems? If you want to get started with the help of experts read more over here: Fix Your SQL Server.

Reference: Pinal Dave (https://blog.sqlauthority.com)