skip to Main Content

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 [email protected]

About the Author

Geoff Ballard

As Co-Founder of Ballard Chalmers, Geoff is the company’s CTO, directing technical strategy, overseeing technical consultants, managing larger development projects and ensuring technical delivery quality standards.

Education, Membership & Awards

Geoff graduated from the University of London with a BSc (Hons) in Mathematics and computing and a Masters degree in Database Management Systems. Additionally, he is a certified SQL Server Development, Microsoft Certified IT Professional (MCITP) and Microsoft Certified Technical Specialist (MCTS).

His professional memberships include Charted Engineer (Software), Member of the British Computer Society (MBCS), Member of European Federation of Engineers (FEANI) and Fellow of the Institute of Analysts and Programmers (FIAP).

 

Our Technologies

HAVE YOU SEEN OUR NEWSLETTER?

ABOUT US

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

01342 410223

[email protected]

 

CONTACT US






This form collects your name and details so that we may answer you. Check out our Privacy Policy for full details on how we protect and manage your data.

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

© 2020 Ballard Chalmers Ltd. All Rights Reserved

Back To Top