How to set no more new connection to a database?

Question

Post reply

How to set no more new connection to a database?

SQL ORACLE

One Orange Chip

Points: 27807
More actions
August 23, 2011 at 11:01 am

#242084

Many thanks in advance for any input.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

huh? Are you asking how to make a database not accept connections?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 2

you could do this:

ALTER DATABASE DBNAME SET RESTRICTED_USER

I think that will prevent any other connections from hitting the database unless they are a sysadmin...you don't have everyone logging inas 'sa', right? i do not believe it causes non-admins from dropping their existing connections.

optionally, you can kick everyone out and also restrict it to sysadmins:

ALTER DATABASE DBNAME SET RESTRICTED_USER WITH ROLLBACK IMMEDIATEand if you are currently connected yourself in an SSMS window, you could set it to single user to get exclusive access.

ALTER DATABASE DBNAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!