Contained databases are new in 2012 and we can
define as "A contained database is a database that is isolated from
other databases and from the instance of SQL Server that hosts the database."
SQL Server 2012 helps user to isolate their database from the instance in 4
ways.
1-
Much of the metadata that describes a database
is maintained in the database. (In addition to, or instead of, maintaining
metadata in the master database.)
2-
All metadata are defined using the same
collation.
3-
User authentication can be performed by the
database, reducing the databases dependency on the logins of the instance of
SQL Server.
4-
The SQL Server environment (DMV's, XEvents,
etc.) reports and can act upon containment information.
The following terms
apply to the contained database model.
Database boundary
The boundary between a database and the
instance of SQL Server. The boundary between a database and other databases.
Contained
An element that exists entirely in the
database boundary.
Uncontained
An element that crosses the database boundary.
Non-contained database
A database that has containment set to NONE.
All databases in versions earlier than SQL Server 2012 are non-contained. By
default, all SQL Server 2012 databases have a containment set to NONE.
Partially contained
database
A partially contained database is a contained
database that can allow some features that cross the database boundary. SQL
Server includes the ability to determine when the containment boundary is
crossed.
Contained user
There are two types of users for contained
databases.
·
Contained
database user with password
Contained database users with passwords are
authenticated by the database.
·
Windows
principals
Authorized Windows users and members of
authorized Windows groups can connect directly to the database and do not need
logins in the master database. The database trusts the
authentication by Windows.
Enabling contained database support from server properties
Or
XEC sp_configure 'show advanced', 1
GO
RECONFIGURE
GO
GO
RECONFIGURE
GO
EXEC sp_configure 'contained database
authentication', 1
GO
RECONFIGURE
GO
GO
RECONFIGURE
GO
Set option to Partial
Some features of partially contained
databases, such as storing metadata in the database, apply to all SQL Server
2012 databases. Some benefits of partially contained databases, such as
database level authentication and catalog collation, must be enabled before
they are available
Creating a user that has no access outside its database is
contained database user
User can create User, database role, application role,
schema and audit specification for only one database and it runs within this
database. Migrating of this database to other place only needs to backup and
restore and you do not need to migrate users and other options with it.
You can create multiple types of users
Creating user with password to reside in local database
Assigning proper database rights, for admin user,
it can only be access this database and perform all tasks.
Connecting to a contained database
You need to mention database name before connected to
contained database
And you will only get your database. This is the best case
for testing on production machine before deployment.
Resources:
No comments:
Post a Comment