Skip to content

Creating a User without a Login – Contained Databases

April 16, 2012

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.


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:


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.


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

About these ads

From → Blog

One Comment

Trackbacks & Pingbacks

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

Comments are closed.


Get every new post delivered to your Inbox.

Join 5,130 other followers

%d bloggers like this: