Contained Databases

Contained Databases

Ever since the first version of SQL Server was released there has been a distinction between a server login and a database user.

Server logins are recorded in the master database and control connection to the server. They consist of:

·         Windows Logins

·         Windows Groups

·         SQL Server Logins plus password

Database users are recorded in each database and manage access to the database and permissions. Each database user is associated with a server login.

The screen shot below shows a login called INTERNAL\Administrator mapped to a database user called ReportUser in database ReportServer  and to database user dbo in a number of other databases:

The concept of having server logins and database users has served SQL Server well over the years, but it does have a problem because it ties the database to the server.

Moving a database to another server causes an issue because the connection between the database user and the server login is broken and the user becomes an orphan. The result is that it is no longer possible to connect to the database as that user. Permissions are attached to the database user and are stored in the database and so moving the database to another server does transfer all the permissions, it’s just the login that causes the issue.

Microsoft has a solution for this issue in the form of a stored procedure that can be used to fix the orphaned users:

·         sp_change_users_login 'report'

o   Reports on orphaned users

·         sp_change_users 'auto_fix', 'user', ‘login’, ‘password’

o   Fixes a single user by associating it with a login or creating a login if it does not exist

o   The default login name is the same as the user

o   Wildcards are supported in the user name so multiple users can be fixed in one go

·         sp_change_users 'update_one', 'user', 'login'

o   Fixes a user by connecting it to a login

Although sp_change_users_login is one solution to the login issue, SQL Server 2012 has a better solution in the form of Contained Databases. These allow authentication to a database without a login and as a result contained databases can be transferred between servers without any issues.

To create a contained database, or restore or attach one, it is first necessary to configure the server to allow contained databases on the server. This is disabled by default:

sp_configure ‘contained database authentication’, 1

RECONFIGURE

Then to create a contained database use:

                CREATE DATABASE ContainedDB
                CONTAINMENT = PARTIAL

The only other option for the CONTAINMENT clause is NONE.

Users can be created in the new database without an associated login. For example, the following commands create one SQL user and one Windows user:

                USE ContainmentDB

GO

CREATE USER [SQLUser1] WITH PASSWORD N'######'

CREATE USER [INTERNAL\WindowsUser1] FROM WINDOWS

Then when connecting to a contained database just specify the database as part of the connection and no server login is required.

In a .NET application the database is included as part of the connection string:

Server=.; Database= ContainedDB; User ID=SQLUser1; Password=######;

Trusted_Connection=False;

In SQL Server Management Studio the database is selected on the Connection Properties tab:

Contained Database is a very simple but effective concept and makes databases independent of the SQL Server they are hosted on.