SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.


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.

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

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...