Last week
I wrote about what is a contained database, the benefits and challenges, today
as promised I'll show you how to setup one, how to configure a user and how to
connect to it so let's get started.
Step 1
After logging in to
your SQL Instance, go to the instance properties.
In the properties
page, select advanced and in the first section choose "Enable Contained
Databases = True" With this you will be enabling the feature at the
instance level so that you can setup your databases this type.
Step 3
Go to the database
that you want to convert to a contained database and click properties, then go
to options and in the Containment Type choose Partial.
With the above steps
you have now your first contained database, however at this point there is
nothing different as if you try to connect to the database with the accounts or
users that are already granted at the instance level you will see no difference,
for you to see the difference you will need to configure one use or account at
the database level as follow.
Step 4
Go to the security
folder inside the contained database, go to the Users folder and choose
"Create new User", it will prompt you the following window where you
will create your user, remember to configure the privileges that you will allow
it to have, and always remember the best practice is to follow the least
privileged principle.
Ok so you have now
your user created, let's try to connect
Step 5.1 connecting to SQL Server as usual
Wait, what happened?
We created the user, now it is not able to connect?
Step 5.2 To take advantage of the containment,
you will need to select directly the database that you want to connect, you can
do this by clicking on the "Options Button" at the bottom right
corner of the screen, the logon window will change, so then you will need to go
to the Connection Properties tab and in the "Connect to Database"
section, you will need to write the Database name, it shows the option to
lookup, however if you are not logged in it will tell you that you need to
login first and you will end up in a circle where you can't authenticate, so
better write down the database name so you can enter it here.
Step 6 Welcome you are now connected directly to your database. Can you see the difference? With an account that is granted at the server level you can see all the databases in the SQL Instance, however when you are connected to your contained database, it shows you only the database that you have permissions, creating an isolation of the environment.
Now lest do some
tests, imaging that you are sharing the account with someone from the
production control team and that person has a little bit of knowledge in SQL,
and tries to discover it there are more databases in the instance, the query
will only return 3, Master, tempdb and the contained database it has
permissions, why this 3? Well, master has the metadata that it needs to work
with, if you create temp objects you will need to rely on the tempdb, but
that's it.
For the second test, I created another contained database and tried to run queries against, however it doesn't lets me do it, why? It was not mentioned in last week's post but other of the limitations is that you can't run queries across different databases, unless you have the guest account enabled, which is not this case and that strongly not recommend.
Summary:
Contained databases is a feature that provides isolation between environments
if you are running databases that requires this kind of separation, also by
using this feature you don't have to worry about migrating users, roles, etc.
when you move your database from one server to another, either because you are
migrating, upgrading or recovering your server, also this portability eases the
configuration and security administration when you have Always On Availability
groups configured for you HA/DR environments.
