Creating User for a single contained database

Question

I am pretty new working with MSSQL and have 2012 version at the moment. What I'm trying to do is create a user for a contained database, and give that user access only to that database.

I will invoke this SQL from JAVA using a prepared statement.

Answer 1

To create a contained user in SQL Server 2012 that authenticates at the database, you can do the following:

use YourDatabase
go

create user YourContainedUser
with password = 'yourPassword'
go

You will have to ensure that contained database authentication is enabled on the instance first, and that the database is set with partial containment:

exec sp_configure 'contained database authentication', 1
go
reconfigure
go

alter database YourDatabase
set containment = partial
go

Once those above commands are executed you should be able to create your contained user with the CREATE USER statement as explained above.

EDIT: Great comment and points by @AaronBertrand:

Also your connection string absolutely must use the database name as part of the credentials - if you don't specify the database, SQL Server isn't going to check all the databases and try to figure out which one you may have meant. And your code can't perform any cross-database queries in case that limitation isn't clear.