Contained Database is independent of
the instance of the SQL Server on which it is hosted and also isolated from other
databases.
SQL server 2012 allows the user to
isolate the contained database in following ways
1. All the metadata is maintained in
contained database instead of master database
2.. All metadata are defined using same
collation
3.
User authentication is performed by
database instead of the server login
in this way contained database becomes fully portable without having the issue of orphaned users.
Process to implement contained
database:
1. Enable setting
for contained database authentication.
2. Create a Contained database named ContainedDB' by
selecting containment type='Partial' in option tab.
3. Create database user named ContainedDBUser with SQL
Server authentication with no link at instance level.
4. While connecting to containedDB database, connect it
by selecting in options of 'connect to server' with the new user named
ContainedDBUser.
Use master
GO
sp_configure 'show
advanced options',1
GO
RECONFIGURE WITH
OVERRIDE
GO
sp_configure 'contained
database authentication', 1
GO
RECONFIGURE WITH
OVERRIDE
GO
sp_configure 'show
advanced options', 0
GO
RECONFIGURE WITH
OVERRIDE
GO
Let us
do it practically.
1. Create
ContainedDB
2. In options
tab select containment type to partial.
3. Create database user named ContainedDBUser
with usertype as SQL USER WITH Password.
4. Connect to ContainedDB database using the
above created user ContainedDBUser by mentioning details through options.
in login mention ContainedDBUser
5. In connection properties tab, connect to
database ContainedDB
66. We will get ContainedDB connection as
below.
f
Please try above scenarios in Development environment only.
No comments :
Post a Comment