We can restrict SQL database users to fire some critical queries, e.g., Update, Delete, etc., through SQL Server Management Studio.
This article will guide you on how to assign specific permissions to SQL Server database users for accessing a table residing in the database. Kindly follow the below-mentioned steps to proceed:
1. Log in to the SQL Server via SQL Server Management Studio using the login details below, as shown in the following image. Please refer to connect MsSQL database using management studio for more details.
- Server type: Database Engine
- Server name: IP address,1533
- Authentication: SQL Server Authentication
- Login: SQL Server database user
- Password: As set by you
2. Select your SQL database, Expand the database name, then select the table residing in that database for which you want to assign the required permissions to that database user, as shown in the following image:
3. Right-click on the table and select Properties. Click on the Permissions option from the Table Properties dialog box.
4. Click on the Search button and click on the Object Types button.
5. Now, check the Users option and click on the OK button, as shown in the following image:
6. Click the Browse button, select your SQL database user, and click OK.
7. Once again, click the OK button from the "Select Users or Roles" Dialog box.
8. Assign appropriate permissions to the user, as shown in the following image:
By referring to these steps, you can assign particular permissions to the specific database user in SQL Server.
Note: The above steps are performed on SQL Server 2008 R2 Express Edition.
→ Looking to change DB user schema to DBO in MSSQL? Please refer to the change DB schema for more details.
