| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | helpviewer_keywords | ||
|---|---|---|---|---|---|---|---|---|---|---|
contained database authentication (server configuration option) |
Learn about the contained database authentication option. See how to turn it on so that you can attach contained databases to the SQL Server Database Engine. |
rwestMSFT |
randolphwest |
03/02/2017 |
sql |
configuration |
conceptual |
|
[!INCLUDE SQL Server]
Use the contained database authentication option to enable contained databases on the instance of [!INCLUDEssDEnoversion].
This server option allows you to control contained database authentication.
-
When contained database authentication is off (0) for the instance, contained databases cannot be created, or attached to the [!INCLUDEssDE].
-
When contained database authentication is on (1) for the instance, contained databases can be created, or attached to the [!INCLUDEssDE].
A contained database includes all database settings and metadata required to define the database and has no configuration dependencies on the instance of the [!INCLUDEssDE] where the database is installed. Users can connect to the database without authenticating a login at the [!INCLUDEssDE] level. Isolating the database from the Database Engine makes it possible to easily move the database to another instance of [!INCLUDEssNoVersion]. Including all the database settings in the database enables database owners to manage all the configuration settings for the database. For more information about contained databases, see Contained Databases.
Note
Contained databases are always enabled for [!INCLUDEssSDS_md] and [!INCLUDEssazuresynapse-md] and cannot be disabled.
If an instance of [!INCLUDEssNoVersion] has any contained databases the contained database authentication setting can be set to 0 by using the RECONFIGURE WITH OVERRIDE statement. Setting contained database authentication to 0 will disable contained database authentication for the contained databases.
Important
When contained databases are enabled, database users with the ALTER ANY USER permission, such as members of the db_owner and db_accessadmin database roles, can grant access to databases and by doing so, grant access to the instance of [!INCLUDEssNoVersion]. This means that control over access to the server is no longer limited to members of the sysadmin and securityadmin fixed server role, and logins with the server level CONTROL SERVER and ALTER ANY LOGIN permission. Before allowing contained databases, you should understand the risks associated with contained databases. For more information, see Security Best Practices with Contained Databases.
The following example enables contained databases on the instance of the [!INCLUDEssDE].
sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO sp_configure (Transact-SQL)
RECONFIGURE (Transact-SQL)
Server Configuration Options (SQL Server)