SQL Server 2012 introduced a new feature called Contained Databases.
Database is a collection of data objects, views, sps and users also. If you move database from ServerA to ServerB then we might not able to access the database using ServerB Logins because there is a dependency between server and database, logins will be stored under server level and users are stored at database level.
Hence database is not a individual component, This contained database can break the barrier of dependency.
If we migrate the contained database into other server (from ServerA to ServerB) then we need not to createa any logins to make use of it.
Steps To Create Contained Database
I) sp_configure 'contained database authentication',1
reconfigure
II) create database KalyanDB containment = partial -- It is used to make our database into partial contained db. We can change this while creating new database also from the UI, In options tab under containment type, we can select whatever containment is requied.
III) create a database user (DatabaseName - Security - Users - New User)
Unlike previous editions, this wizard will have so many options in UserType list (SQL User With Login, SQL User with Password etc.,) -- Select SQL User with Password option
create user kalyantest with password = password
IV) Select Membership tab --> select whatever database role you want for that login
To Test this feature on the same server
I) Click on Connect -> Select SQL Server Authentication
Login - KalyanTest
Password - password
II) Click on Options
III) Under Connection Properties
Connect to database -- Select name of your contained database -- ok
Iv) Now you can able to connect to the contained database, eventhough you dont have login created on the server.
Steps To Create Contained Database
I) sp_configure 'contained database authentication',1
reconfigure
II) create database KalyanDB containment = partial -- It is used to make our database into partial contained db. We can change this while creating new database also from the UI, In options tab under containment type, we can select whatever containment is requied.
III) create a database user (DatabaseName - Security - Users - New User)
Unlike previous editions, this wizard will have so many options in UserType list (SQL User With Login, SQL User with Password etc.,) -- Select SQL User with Password option
create user kalyantest with password = password
IV) Select Membership tab --> select whatever database role you want for that login
To Test this feature on the same server
I) Click on Connect -> Select SQL Server Authentication
Login - KalyanTest
Password - password
II) Click on Options
III) Under Connection Properties
Connect to database -- Select name of your contained database -- ok
Iv) Now you can able to connect to the contained database, eventhough you dont have login created on the server.
No comments: