:::: MENU ::::

Configure Database State and restrict access in SQL Server

  • Nov 05 / 2008
  • 0
dbDigger, Logins and Users, Security and Permissions

Configure Database State and restrict access in SQL Server

Database Read-Only (READ_ONLY or READ_WRITE)
You can use Database Read-Only option to control whether updates are allowed on the database. When set to TRUE in SQL Server Management Studio or ON when using the ALTER DATABASE command, users can only read from the database and are not permitted to modify data. When set to FALSE, the read and write operations are permitted on the database. To change the state of this option, you must have exclusive access to the database.

Database State (DB_STATE_OPTION)
You can use Database State option to control the state of the database. When set to NORMAL, the database is fully operational and available for use. When the database is set to CLOSED, the database is shut down cleanly and marked offline. The database cannot be accessed or modified while in this state. When set to EMERGENCY, the database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role. The EMERGENCY database state is used primarily for troubleshooting purposes.

Restrict Access can be used this option to control access to the database. In MULTIPLE mode all users are allowed to connect to the database as long as they have the appropriate permissions. Conversely, in the SINGLE mode only one user is permitted to access the database at a time. In the RESTRICTED mode only members of the db_owner, dbcreator, and sysadmin roles can connect to the database.

Consult us to explore the Databases. Contact us