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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.

One Response to Creating a User without a Login – Contained Databases

  1. Pingback: Contained Databases in SQL Server 2012 « Voice of the DBA

Comments are closed.