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.
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:
CREATE USER [DKRSQL2012\Andy] GO
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.