Contained Database Users in SQL
Contained database users are users that are configured directly at the database level, and don’t require an associated login in the master database.
The benefit of this approach is that it makes your database more portable - and it also simplifies database deployments a little.
It looks like contained databases have been available for years now, but I’ve only just become aware of contained database users in the last couple months - so I figure, maybe I’m not the only one..
If you have docker installed locally and would to play along at home, here is a quick docker-compose file to get you up and running with your own local SQL instance.
Once the SQL instance is up and running, connect to the instance using the credentials in the docker-compose, and run the following script to enable contained database authentication.
The Traditional Login + User Model
The traditional model for providing a user with access to a database was a two step process.
- Create a
loginin the master database
- Create a
userin the target database mapped to the login from step 1.
The login is to provide
authentication to the SQL
instance, whereas the user provides
authorisation to the SQL
Here is a sample SQL script you can run to see it in action.
Using SSMS, you should see something like this.
The Contained Database User Model
In contrast, the contained database user model only needs the
CREATE USER, with the addition of password parameter.
Producing the following
Verify the User
Verify that you can connect to the database - make sure to set the database name in the connection properties or you will get an error.