Database Accessibility

                There are going to be many times throughout the administration of databases that a DBA will need to control how the database is accessed.  Sometimes we need to shut it down altogether, or leave it open just enough to troubleshoot.  There are a few variations that can give you the end result for database accessibility.

Database State

                The database state controls whether or not the database is accessible, and the level of accessibility.  There are three options for database state

·         ONLINE – the database is accessible for user connections

·         OFFLINE – the database is shut down and can’t receive user connections

·         EMERGENCY – the database is READ_ONLY, and logging is disabled

Here are some examples in action:

Setting a database OFFLINE: (two variations for rolling back open transactions.  See code comments for descriptions of each)

use master

go

-- take database immediately offline

-- rollback all transactions right away

alter database YourDatabase

set offline

with rollback immediate

go

-- take database offline when all open trans are

--  committed or rolled back

-- rollback all transactions after 30 seconds

alter database YourDatabase

set offline

with rollback after 30

go

Setting a database ONLINE:

use master

go

alter database YourDatabase

set online

go

Setting a database in EMERGENCY:

alter database YourDatabase

set emergency

with rollback immediate

go

Database Updateability

                There are two options to control whether or not write operations can be executed on a database:

          ·         READ_ONLY – data can be read but not written

          ·         READ_WRITE – data can be read and written to (given appropriate privileges)

Put database in READ_ONLY:

alter database YourDatabase

set read_only

with rollback immediate

go

Put database in READ_WRITE:

alter database YourDatabase

set read_write

with rollback immediate

go

Database User Access

                There are no doubt times when you’ll absolutely need to change how many users and if only certain roles can connect to the database.  There are three options that control database user access:

          ·         MULTI_USER – all users are allowed to connect to the database (given appropriate privileges)

          ·         SINGLE_USER – only one user (with privileges to connect) can be connected at any given time

          ·         RESTRICTED_USER – only members of the following roles can connect to the database: db_owner (database role), sysadmin (server role), and dbcreator (server role)

Set database for MULTI_USER access:

alter database AdventureWorks2012

set multi_user

go

Set database for SINGLE_USER access:

alter database AdventureWorks2012

set single_user

with rollback immediate

go

Set database for RESTRICTED_USER access:

alter database AdventureWorks2012

set restricted_user

with rollback immediate

go

                In summary, this post has showed you numerous ways to control the way users, and who (and how many) is able to access the database.  Any comments, questions, or problems you can leave a comment below or email me at sqlsalt@gmail.com.

                As for the code to this post, it is kept in my SQLSalt GitHub repository, as follows:

Database_Accessibility_OnlineOfflineEmergency.sql

Database_Accessibility_ReadOnlyReadWrite.sql

Database_Accessibility_MultiUserSingleUserRestrictedUser.sql