How to change user access mode in MSSQL Server?

MSSQL Server provides a feature to restrict your Database access for users. It can be performed by selecting appropriate access mode for your database. By default, there will be 3 access modes in SQL Server. Single User, Multiple User and Restricted User.

Option 1 : Change User access mode from MSSQL Management Studio 

Following are the steps to change the user access mode in MSSQL Server.

  1. Login to your MSSQL Server using the SQL Management studio.

  2. Right Click on the Database which user access mode you are looking to change and select properties.



  3. Click on Options.



  4. From other options drop-down >> Go to State >> Restrict Access

  5. At Restrict Access Drop-Down, select the option (single_user, multi_user and restricted_user) as per your requirement and click on OK Button.

Option 2 : Change User access mode Using Transact SQL Command 

  1. Login to your SQL Server with MSSQL Management Studio.

  2. Click on New query.



  3. Hit the below query by changing your database name and required user access mode.

    ALTER DATABASE databasename SET user access mode;


  4. Click on Execute Button.

          This is how you can change your user access for your Database.

  • 0 Users Found This Useful

Was this answer helpful?

Related Articles

How to Backup MSSQL Database from SQL Server Management Studio?

To take MS SQL database backup, first you need to connect to MS SQL Server through SQL Server...

How to install MS SQL Server 2016?

Microsoft® SQL Server® 2016 is the latest version of Microsoft's relational database management...

How do I connect to MSSQL Server 2012 from SQL Server Management Studio?

This article will guide you on how to connect to MS SQL Server 2012 from SQL Server Management...

Comparison of MS SQL Server 2014 Express Edition V/s. MS SQL Server 2014 Web Edition

Following is a brief comparison between MSSQL Server 2014 Express Edition and MSSQL Server 2014...

Microsoft SQL Server 2014 Recommendation

The SQL Server 2014 Express is yet another feature-rich data management system from Microsoft....