ballardchalmers - home

Contact us 01342 410223

Blog

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:

Contained Databases 1 

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 Databases 2

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

By Geoff Ballard, Chief Technical Officer

Have a comment? Let us know below, or send an email to enquiries@ballardchalmers.com

Comment on this article

About

Ballard Chalmers is one of the UK’s leading enterprise software engineering companies, specialising in development for the Microsoft Enterprise Application Platform. We are principally dedicated to software development and integration for the Microsoft Cloud, as well as on-premises and hybrid systems. Our expertise is in Microsoft .NET, SQL Server, BizTalk Server and SharePoint Server.

Let's talk

  01342 410223

Find out more

Thank you for contacting us.

We will be in touch with you shortly.