9

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.

12

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.

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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