Having read in a book of Patrick LeBlank "Microsoft® SQL Server® 2012 Step by Step":

  • "Benefits and limitations of contained databases

    As with most features, there are benefits and limitations to contained database security. Most of the limitations are specific to containment’s operability with other SQL Server features. If database containment is enabled, you cannot use change data capture, change tracking, and replication"

I wonder:
does setting "Enable contained Databases" to True on server

enter image description here

incur limitations on the whole server, the instance of server or specific database created as contained one?

enter image description here

Apparently, it is only to contained database but, then, why it should be enabled server-wide first?
and what does it incur (for example, if I enable it on server but did not enable it on any particular database)?


Instance-wide settings are typical when the database engine can optimize code paths if the setting is off. In this instance, having contained database authentication = 1 means the database engine must perform checks on each database that it normally wouldn't need to, in addition to checking login requests against any database that has containment enabled.

The real impact on this is probably quite negligible; if you are forecasting that at some near-term future point you might need to enable containment for a specific database, you may consider enabling the instance-wide setting during some maintenance window.

  • Sounds like it is possible to use contained database without switching on Enable Contained Databases to True . But, in such case, it is impossible to enable the containment on any database as well as to login thru a contained user to databases with containment already enabled before – Fulproof Nov 21 '13 at 1:51
  • When contained database authentication is off (0) for the instance, contained databases cannot be created, or attached to the Database Engine. technet.microsoft.com/en-us/library/ff929237.aspx – Max Vernon Nov 21 '13 at 15:13

why it should be enabled server-wide first?

Because it is a general switch whether contained databases are ALLOWED on the server. Put it to now then you cna not use that feature.

And that basically is it - once you enable it, the server can handle partially contained databases. What a partially contained database is is - something you can read up in the documentation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.