MSSQL%20Interview%20Questions%20and%20Answers
Question: How to set database to be SINGLE_USER in MS SQL Server?Answer: Databases in SQL Server have three user access options:* MULTI_USER - All users that have the appropriate permissions to connect to the database are allowed. This is the default. * SINGLE_USER - One user at a time is allowed to connect to the database. All other user connections are broken. * RESTRICTED_USER - Only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles are allowed to connect to the database, but it does not limit their number. You can use the "ALTER DATABASE" to change database user access options as shown in the tutorial below: USE withoutbookDatabase GO ALTER DATABASE withoutbookDatabase SET SINGLE_USER GO Now connect to server with another client session and try: USE withoutbookDatabase GO Msg 924, Level 14, State 1, Line 1 Database 'withoutbookDatabase' is already open and can only have one user at a time. Go back to the first session and re-set the database to MULTI_USER: ALTER DATABASE withoutbookDatabase SET MULTI_USER GO |
Is it helpful?
Yes
No
Most helpful rated by users:
- What is Microsoft SQL Server?
- What is the simplest way to create a new database in MS SQL Server?
- How to delete a database in MS SQL Server?
- How to download and install Microsoft .NET Framework Version 2.0?
- How To Get a List of Columns using the "sp_help" Stored Procedure in MS SQL Server?