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.

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.

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.

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

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.

This looks the same when I accept a user

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.

Filed under: Blog Tagged: ContainedDatabases, security, sql server, syndicated