Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Database User and Programming Tips

Posted December 4, 2017

WEBINAR: On-demand webcast

How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >

Quickly Restricting Database Access

By Greg Larsen

There may be times when you need to perform some maintenance against a database that requires you to keep normal database users from connecting to your database, while the maintenance is being performed.  You want to stop users from connecting right away, but you want the database to be in a state that you can perform maintenance.  What do you do?

There are three different modes that a database can be in: MULTI_USER, RESTRICTED_USER, or SINGLE_USER.  By putting the database in RESTRICTED_USER mode you will keep normal users from being able to connect to your databases.  This mode doesn’t restrict any users that are in the db_owner role, or sysadmin role from connecting to the database.   You can put the database in RESTRICTED_USER mode by running the following command:

ALTER DATABASE MyDatabase SET RESTRICTED_USER;

Once you put the database in RESTRICTED_USER mode, a database user that is not in the db_owner or sysadmin role will not be able to connect to the database.  Keep in mind that having your database in RESTRICTED_USER mode doesn’t kick users out of the database if they are already connected.  Therefore, if you need to perform an action that requires all users to be out of the database you would need to first terminate all SPIDs that are using the database.  You can do that by issuing  the KILL command.

Putting the database in RESTRICTED_USER mode will not keep everyone out.  People in the db_owner role or sysadmin role can still connect to the database.  If you really want only yourself in the database, you will need to place the database in SINGLE_USER mode.  To put the database in SINGLE_USER mode, and kick all the users out of the database you can run the following the following TSQL command:

ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

See all articles by Greg Larsen



Database User and Programming Tips Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.