Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

New features in SQL 2012, the contained database

By Daniel Calbimonte,

This time we are going to talk about the contained database and the problems that it solves.

I guess all of us have had the same problem. When we restore a backup from one SQL Server to another, we cannot logon because the logins are stored in the master database. When we move a database in another server, the logins are not included.

In SQL Server we need logins and user databases. When we restored a database, we may need to move the logins as well. In SQL Server there were many ways to move the logins. The first method is the copy database wizard using the SQL Server Management Studio. The image below shows how you can launch the wizard.

In the steps of the wizard, we have an option to copy additional objects like the logins, master stored procedures, jobs and other objects, as shown below.

Another method is the SQL Server Data Tools (SSDT) in SQL Server 2012 or in Business Intelligence Development Studio (BIDS) in earlier versions. You can create a SQL Server Integration Services project and use the transfer logins tasks as shown below:

The third method is the favorite one and it uses stored procedures. For more information you can refer to this link: http://support.microsoft.com/kb/246133

As you notice, it was not easy to transfer the logins. That’s why Contained Databases were added to SQL Server 2012.

Contained databases

The main idea is to remove the independency between the Database Engine and the database. The contained databases are independent databases that do not require a login. Let’s start with an example.

Getting started

In this example I am going to create a contained database, backup it and restore it in another SQL instance. To start we are going to enable the contained database authentication in both SQL Servers:

exec sp_configure 'contained database authentication', 1;
GO
RECONFIGURE ;
GO

Now let’s create a database:

CREATE DATABASE testCD

Let’s enable the contained database in the testCD database:

USE [master]
GO
ALTER DATABASE [TestCD] SET CONTAINMENT = PARTIAL
GO

We are going to create 1 user in the new database:

USE [testCD]
GO
CREATE USER [sqlCentral] WITH PASSWORD=N'sqlcentral'
GO

The next step is to backup the database:

BACKUP DATABASE [testCD] TO  DISK = N'C:\backup\testCD.bak'

On another SQL Server, make sure that you enabled the contained database property for the instance and then restore the database:

USE [master]
RESTORE DATABASE [testCD] FROM  DISK = N'C:\backup\testCD.bak'

And the final step is to login to the testCD database using the user created:

You will need to press the Option button and go to the connection properties. In the Connect to database, write testCD and finally press the connect button.

If everything is OK, you will be able to connect to the database without a login !

Conclusion

In this example we explained the main problems caused by the logins and databases and we also reviewed how to solve the problem in earlier versions and finally with SQL 2012.

References

Total article views: 10346 | Views in the last 30 days: 14
 
Related Articles
BLOG

SQL Server 2012 || Contained Database

Contained databases are the new feature in SQL Server 2012 and are defined on BOL (http://technet.mi...

BLOG

Creating a User without a Login – Contained Databases

In SQL Server 2012, we have a new feature: partially contained databases. In a previous post, I show...

BLOG

Denali — Day 7: Contained Database

  Denali – Day 7 : Contained Database Contained database is another new feature for sql server...

BLOG

Understanding Contained Database in SQL Server 2012

SQL Server 2012 supports contained databases and partially contained databases, which provide a high...

FORUM

moving databases,logins

moving database,logins

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones