Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

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.


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

Comments

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

Loading comments...