What is SQLSerrver2012 Contained Database Authentication and how it works?

What is Contained Database Authentication?

This is one of the new feature introduced in SQLServer 2012, before we actually get in to it first I would try to explain drawback of login/users especially when you move the database from one server to another server.

There are different methods one can use to copy/move a database, when you move/copy a database to other sql server, you need to sync the login and users, if the login is not there on destination server you have to create and you need to give right access  if login is there but user doesn’t have proper access…… so DBA is required to resolve those login/user access because prior to SQL2012 logins will be only created at Syslogins table in Master database and when you give access to particular databases those logins will be created as a Users in respective databases. When you move databases from one location to another - users will go along with databases but not logins. When it comes to accessing SQL server first it verifies with syslogins , if it is there then only it is allowed to connect.

This has been greatly over come with new feature Contained Database Authentication, in this users logins are directly stored on user Databases (instead of syslogins in master database which is default in prior versions). It is very secure because users can only perform DML operations inside the user databases and not database instance level operations. It also reduces the need to login to the database instance and avoid orphaned or unused logins in the database instance. This feature is used in AlwaysOn to facilitate better portability of user databases among servers in the case of server failover without the need to configure logins for all database servers in the cluster.

Example:

Step1: Enable Contained Database Authentication option on SQLServer.

Ø  Connect to SQLServer through SSMS

Ø  Right click on instance and go to Properties à Advanced Page

Ø  Select True on ”Enable Contained Database”

Step2: Create a Database set option Containment type with “Partial”.

Ø  Connect to SQLServer through SSMS

Ø  Right click on databases and create a new database “Contained_DB” while creating go to Option

Ø  Select “Partial” for Containment Type.

Step3: Create a user “Contained_User” in Contained_DB

Ø  Right click on “Contained_DB” click on Security – USER

Ø  Right click on User and click on new user

Ø  Select “SQL user with Password” for user type and furnish User name Contained_User and type password to complete the user creation.

Step4 : How to connect with this user.

Ø  Connect to SSMS

Ø    click on  Connection Property Tab

Ø  Type database name as Contained_DB in connect to database box

Ø  Click on login tab Type the SQLServername

Ø Select authentication to “SQL”

Ø  Type user name as Contained_User and password

Ø  Click on Connect