Voice of the DBA

Tag: contained databases

  • 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 showed how to enable this, and this post will look at one of the advantages of contained databases: users without logins.

    Contained Users

    One of the problems in non-contained databases is the fact that when a database is moved or restored, the login mapping to the user in the database doesn’t always transfer cleanly. Microsoft has sp_help_revlogin and sp_change_users_login to help fix this, but in a DR situation, or in a crisis, this may not work. It’s also a hassle.

    Contained users help fix this. They are users that exist within the database, and do not require a login mapping. The server level authentication will transfer to a database level authentication, if the database has the partial containment option set.

    To create a contained user, you can use the GUI, or T-SQL, both of which are easy and I’ll show them below:

    SSMS Contained User

    If you right click the Users folder (under Security) in a database, you can select the New User option.

    cdb3

    When this appears, you can then use the drop down to select a User with a Password option for a SQL Server user that is contained inside a database.

    cdb4

    The traditional user is a user with a login. Here’s the dialog from SSMS 2008, with no option for a user without a password.

    cdb8

    Back to 2012, I can enter a user name and password, and then I have a user in my database.

    cdb5

    The process for a Windows user (again, without a login) is similar. I can select a “Windows User” and then select the ellipsis by the User name and search for an AD user.

    cdb6

    This looks the same when I accept a user

    cdb7

    I can set a default schema here, and a language, but I don’t need the login.

    T-SQL

    The process with T-SQL is the same. The code for the CREATE USER command is simple:

    create user Billy with password = 'Billy2Goat$Gruff'
;

    If I want a Windows user, I can do this:

    CREATE USER [DKRSQL2012\Andy]
GO

    Note that this is domain\user syntax. Some AD tools allow the user@domain syntax, but this isn’t allowed in SQL Server 2012 for the CREATE USER command.

    You can replace the user name with a group at the database level, and the syntax is the same.

    Summary

    That’s it. It’s simple, and in another post, I’ll look at authentication.

  • Enabling Contained Databases in SQL Server 2012

    One of the new features in SQL Server 2012 is the Partially Contained Database feature. I gave a talk on this recently, and I’m looking forward to seeing where this might go in the future.

    This post looks at how you can enable containment in SQL Server 2012. It’s a very simple process, in a couple steps, and I’ll show you both with the GUI and with T-SQL.

    Enable the Instance setting

    There are two levels to enable containment. The first is at the instance level. If you get the server properties for your instance, and look at the advanced tab, you’ll see this:

    cdb1

    You can use the drop down to select the setting you want (true = enabled), but please don’t click the OK button. Always, always, always click the “Script” button. This allows you to see the exact code being run, and then you can also use this in your documentation for change control. Even if this is a development server, get in the habit of scripting things.

    script

    Once you click the script button, you’ll get this T-SQL:

    EXEC sys.sp_configure N'contained database authentication', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

    This will enable containment for the instance and you’ll be halfway there.

    Database Containment

    The database itself also has a containment setting. In this case, you can look at the properties for the database, on the options tab.

    cdb2

    If you look near the top, you have the collation drop down, the Recovery mode, the compatibility model, and then containment is new in 2012. You have “None” and “Partial” available, and clicking Partial will enable containment in  2012. Again, please don’t click OK, but click script.

    The code will appear as below:

    USE [master]
GO
ALTER DATABASE [cdb2] SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO

    You can also set this value when you create a database:

    -- create db with containment
CREATE DATABASE cdb2
 CONTAINMENT = PARTIAL

    That’s it.

    These two items together will enable containment on a database, and then you can work with contained users, something I’ll talk about in another post.

  • Contained Databases in SQL Server 2012

    Abstract:

    One of the problems with databases in SQL Server is the dependency of the database on various parts of the host instance. In SQL Server 2012 there is an enhancement to the database format that allows for partial containment of your database, and will make the movement, migration, and management of the databases much simpler. This session introduces the new database structure and explains how it can be used in your environment.

    This talk looks at the new partially contained databases in SQL Server 2012.

    Agenda:

    • What is a contained database
    • Contained databases in SQL Server 2012
    • The Future

    The talk will demonstrate the issues with uncontained databases, provide demos on how to create and alter contained databases, look at security and collation issues in SQL Server and speculate on the future.

    Level: 200

    Length: 60 minutes

    Slides:

    Demo Code: ContainedDB.zip

    Related Posts

    You can read all the posts I’ve written on contained databases by examining that tag

    Presentations

    You can view my complete speaking schedule here: http://wp.me/P14wgJ-1tV