Information
AUTO_CLOSE determines if a given database is closed or not after a connection terminates. If enabled, subsequent connections to the given database will require the database to be reopened and relevant procedure caches to be rebuilt.
Rationale:
Because authentication of users for contained databases occurs within the database not at the server\instance level, the database must be opened every time to authenticate a user. The frequent opening/closing of the database consumes additional server resources and may contribute to a denial of service.
Solution
Execute the following T-SQL, replacing <database_name> with each database name found by the Audit Procedure:
ALTER DATABASE <database_name> SET AUTO_CLOSE OFF;
Default Value:
By default, the database property AUTO_CLOSE is OFF which is equivalent to is_auto_close_on = 0.
Item Details
Category: CONFIGURATION MANAGEMENT, SYSTEM AND SERVICES ACQUISITION
References: 800-53|CM-1, 800-53|CM-2, 800-53|CM-6, 800-53|CM-7, 800-53|CM-7(1), 800-53|CM-9, 800-53|SA-3, 800-53|SA-8, 800-53|SA-10, CSCv7|5.1
Control ID: e6cf10f661c3e37bccee52a1f89472a07a050979bffbdb62a38bb92e588bea6f