Contain your Enthusiasm

You may be aware that I am still keen on SQL Server and despite the changing nature of my role at Microsoft I still try and keep my hand in with the latest version.  One thing I overlooked until I did SQL Relay was contained database security which is easy to use and can make your life a lot easier.

In previous versions of SQL Server logins are stored in  the master database whether you use windows or SQL Server authentication.  That’s fine until you want your database to move to another instance of SQL Server. For example if you enable mirroring your database could end up running on a secondary server and if you haven’t got a separate process to move the logins across to the secondary this could cause problems if those logins have changed or new ones have been created.

SQL Server 2012 fixes this with contained databases and makes deployment easier by enabling you to put the logins into the application database.  As well as making database more portable it also means that uses connect to specific database rather than to the whole the instance thus limiting what they can do to just that database.  For example they won’t even be aware that there are other databases in that instance. 

For this to work you need to alter the properties of the instance either in options form the GUI:

or using the following TSQL:

EXEC sys.sp_configure ‘Contained database authentication’, 1

GO

RECONFIGURE WITH OVERRIDE

GO

I would then configure my database for contained security again either form the UI..

or in TSQL..

USE Master

ALTER DATABASE [DeepFat] SET ‘containment = PARTIAL WITH NO_WAIT

GO

If I then create a login..

USE [DeepFat]

CREATE USER [Deepfat] WITH PASSWORD = ********

I can do a simple test to see how this works.  I open Management Studio and try and use this login to connect to my instance and it will fail..

because I also need to specify the database I am going to (by expanding the options and selecting the database to connect to) …

If I do that I will be able to connect but all I will see in management studio is my database..

This will work for all the other kinds of logins like windows authentication.  So a nice simple way to contain the enthusiasm of your uses to wander around your SQL servers as well as making the database more portable.

If you want to try this you’ll need to use SQL Server 2012, and you might want to see what else is new in this release on the SQL Server track in the Microsoft Virtual Academy