How to Create a Contained User in a SQL Server Contained Database

Contained Databases feature is introduced in SQL Server 2012.

User authentication for contained users (users with passwords) is performed by the contained database.
Contained users don't have to be granted access at the server level.

To create a contained user with password:

USE Database_Name;
GO
CREATE USER User_Name WITH PASSWORD = 'P@$$w0rd';
GO

To create a Windows Authentication user in the contained database, that doesn't have a SQL Server login:

USE Database_Name;
GO
CREATE USER [DOMAIN\User_Name];
GO

To connect to a contained database using a contained user credentials, you have to specify the contained database in the connection string.
Enter User name and Password:

contained_user_connect_1

Click Options to enter the database to connect to:

contained_user_connect_2

User is successfully connected to the database:

contained_user_connect_3

If you don't specify the database to connect to, connection will fail because SQL Server is looking for a login in master database that doesn't exist:

contained_user_connect_4

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">