How to assign particular permissions to SQL server database user from SQL Server Management Studio?

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 user for accessing a table residing in the database. Kindly follow the below mentioned steps, in order to proceed:

  1. Login to the SQL Server via SQL Server Management Studio, by using the below mentioned login details as shown in the following image:

    • Server type: Database Engine
    • Server name: IP address,1533
    • Authentication: SQL Server Authentication
    • Login: SQL Server database user
    • Password: As per set by you


    Microsoft SQL Server Management Studio Login Screen

  2. Select your SQL database, Expand 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:

    Select Table from Database

  3. Right click on that table, and select Properties. Click on Permissions option from Table Properties dialog box.



  4. Click on Search button and click on Object Types button.

  5. Now, check the Users option and click on OK button, as shown in the following image:

    Open Properties and Go to Permissions Option

  6. Click on Browse button and select your SQL database user and click OK.

    Select SQL Database User to whom You Need to Assign Permissions

  7. Once again click on OK button from "Select Users or Roles" Dialog box.

    Click on OK to Add SQL User

  8. Assign appropriate permissions to the user, as shown in the following image:

    Again Go to Permissions Option and Assign Required Permissions to SQL User


By referring 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.
  • 3 Users Found This Useful

Was this answer helpful?

Related Articles

How to fix "Upgrade Error - Valid Database compatibility level and successful connection rule"?

One of our Windows VPS customers wanted to upgrade SQL Server from SQL Server 2008 R2 to SQL...

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...

How to fix an error "change password failed for login sa"?

Sometimes you receive following error while trying to reset MS SQL sa password via SQL Management...

How to change database language in MS SQL 2012 server?

Following article will guide you to change database language in MS SQL 2012 server. Login to MS...

How to use MSSQL Activity Monitor to find the most expensive query?

A slow and long running SQL query execution consumes much processor, memory, and disk of your...