A Contained Database is a database which contains all the necessary settings and metadata, making database easily portable to another server. This database will contain all the necessary details and will not have to depend on any server where it is installed for anything. You can take this database and move it to another server without having any worries.
Now question raises about the user need on database to login. Contained Database is database that is no more depend on master database for logins. Contained databases contains the user details with database & database user work as login to connect. Once the contained database is moved, the users are moved as well, and users who belong to the contained database will have no access outside the contained database.
In summary, “Database is now self-contained. Database which is ’contained’ will not depend on anything on the server where it is installed.”
Steps:
1) Enable Contained Database
· From SSMS : Steps to enable Contained Database Authentication with SQL Server Management Studio:
1) In Object Explorer, right-click a SQL Server instance, and then click Properties.
2) Select the Advanced page, and in the Containment section, set the Enable Contained Databases to True, and then click OK.
· From T-SQL : Run the following code on SQL Server Denali. This code will enable the settings for the contained database.
sp_configure ‘show advanced options’,1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure ‘contained database authentication’, 1
GO
RECONFIGURE WITH OVERRIDE
GO
2) Create Contained Database
CREATE DATABASE [ContainedDatabase]
CONTAINMENT = Partial
3) Create User in Contained Database
· Create SQL User in Contained Database for which login not exits or not already exits
USE [ContainedDatabase]
GO
CREATE USER ContainedUser
WITH PASSWORD = ‘india@1234’;
GO
· Create Windows User in Contained Database
CREATE USER DOMAINUSER
· Create SQL User in Contained Database for which login
USE ContainedDatabase
GO
sp_migrate_user_to_contained
@username = N'<LOGIN>’,
@rename = N’keep_name’,
@disablelogin = N’do_not_disable_login’
@username = N’user‘ : Name of a user in the current contained database that is mapped to a SQL Server authenticated login. The value is sysname, with a default of NULL.
@rename = ] N’copy_login_name‘ | N’keep_name‘ : When a database user based on a login has a different user name than the login name, use keep_name to retain the database user name during the migration.
@disablelogin = N’disable_login‘ | N’do_not_disable_login‘ :disable_login disables the login in the master database. To connect when the login is disabled, the connection must provide the contained database name as the initial catalog as part of the connection string.
4) Try if this user can access Contained Database
We will attempt to login in the database with default settings (Change the database : SSMS> Option > Connection Properties > Connect Database).
You will notice that the login would be successful in the server. When expanded it, the user will have access to the contained database only, and not to any other database.
5) Possible Risk :
· A user can grant and create contained database users within contained database without the knowledge of the administrators
· A user of contained database can gains the access of other database, if these databases have the guest account enabled.
