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:
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s