Contained Database in SQL Server



This post is moreover interview question requirement then work related. I didn't see people actually using contained databases. 

Why we need Contained databases?
Normally logins are managed at the server level (as logins) and at the database level (as users), so they are defined in two locations.  The mapping that connects logins to users gets lost when a database is moved to a different server, which results in orphaned users.  When moving a database, the logins must be created on the new server, and must match exactly…otherwise this mapping will be broken.Orphaned users can also cause HA/DR to be compromised in this situation, because of trying to manage logins and users separately. Logins have to be synchronized against secondary or fail-over servers to avoid orphaned users.
                         With contained databases, you won’t run into the issue of orphaned users. Contained database users do not have to authenticate with the instance. They don’t have to have a corresponding server login. The contained database user has no dependency on the server. They will connect directly to the database.

For applications to connect, they have to specify the database in the application connection string, and include the parameter called ‘initial catalog’ in order to connect directly to the database.

Benefits with AlwaysOn – in case of failover, contained database users will be able to connect to the secondary without creating logins on the instance hosting the secondary.

Development benefits – because developers don’t always know on which instance their database will be deployed, creating a contained database can limit the environmental effects on the database, and can lessen the work and difficulties for the developer.

Administration – Database owners maintain the database settings in the contained database, instead of master database, so it avoids users having to be given sysadmin permissions.

Collation – temp objects are created with the collation of contained database in the case where the server collation is different from database.  There is no need to COLLATE anymore. There may be some problems if a session accesses both contained and non-contained databases

/**Enabled Advanced options**/
sp_configure ‘show advanced’, 1;
RECONFIGURE WITH OVERRIDE;
go
/**Enabled Database Containment on Server**/
sp_configure ‘contained database authentication’, 1;
RECONFIGURE WITH OVERRIDE;
go

/**Enabled Database Containment**//
USE MASTER
GO
ALTER DATABASE PrimaryDB SET CONTAINMENT=PARTIAL;
GO

/****For cross db connectivity****/
/****Finding SID of CommonUser SID in PrimaryDB*****/

USE PrimaryDB
GO
SELECT name,sid from sys.database_principals where name ='CommonUser'
GO

/*****DROP and RECREATE CommonUser user in SecondaryDB Database*****/

USE [SecondaryDB]
GO
DROP USER CommonUser
GO
CREATE USER [CommonUser]WITH PASSWORD=N'My$Test123', DEFAULT_SCHEMA=[dbo],
sid =0x01050000000000090300000022662BC1922D5C47895DB97345B7215A
GO
ALTER ROLE [db_datareader] ADD MEMBER [CommonUser]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [CommonUser]
GO

Now we need to run the below trust command on both the databases

Alter Database xxxDB Set TRUSTWORTHY ON
GO

Post trust enabled between the databases, we will be able to query both the databases.


1 comment:
Write comments

Please do not enter spam links

Meet US

Services

More Services