Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

migrated from stackoverflow.com Jul 5 '12 at 14:28

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
1  
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. –  Aaron Bertrand Jul 6 '12 at 16:01
    
@AaronBertrand Great point, do you mind if I quote your comment in the answer to ensure its completeness? –  Thomas Stringer Jul 6 '12 at 16:04
    
of course not... –  Aaron Bertrand Jul 6 '12 at 16:04
    
@AaronBertrand Thanks! –  Thomas Stringer Jul 6 '12 at 16:05
    
Thanks for all help, one thing to comment about is , while i was trying to do this with Java JDBC statements. I wasnt able to do it with using PreparedStaments , JDBC was just unhappy with this. so i used a concated string as a statement. –  imellan Jul 15 '12 at 17:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.