Edit

Share via

contained database authentication (server configuration option)

  • Article

Applies to: SQL Server

Use the contained database authentication option to enable contained databases on the instance of SQL Server Database Engine.

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 Database Engine.

  • When contained database authentication is on (1) for the instance, contained databases can be created, or attached to the Database Engine.

A contained database includes all database settings and metadata required to define the database and has no configuration dependencies on the instance of the Database Engine where the database is installed. Users can connect to the database without authenticating a login at the Database Engine level. Isolating the database from the Database Engine makes it possible to easily move the database to another instance of SQL Server. 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 SQL Database and Azure Synapse Analytics and cannot be disabled.

If an instance of SQL Server 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 SQL Server. 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.

Examples

The following example enables contained databases on the instance of the Database Engine.

sp_configure 'contained database authentication', 1;  
GO  
RECONFIGURE;  
GO

See Also

sp_configure (Transact-SQL)
RECONFIGURE (Transact-SQL)
Server Configuration Options (SQL Server)