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.
- Login to your MSSQL Server using the SQL Management studio.
- Right Click on the Database which user access mode you are looking to change and select properties.
- Click on Options.
- From other options drop-down >> Go to State >> Restrict Access
- 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
- Login to your SQL Server with MSSQL Management Studio.
- Click on New query.
- Hit the below query by changing your database name and required user access mode.
ALTER DATABASE databasename SET user access mode;
- Click on Execute Button.
This is how you can change your user access for your Database.