Permissions for Edit Top X Rows in SSMS

ssms

What are the minimum permissions needed to allow Edit Top 200 in SQL Server Management Studio?

Best Answer

Edit Top 200 Rows is simply a GUI way of performing INSERT, UPDATE or DELETE operations on a table, so you only require the same permissions you would require for performing these actions via T-SQL scripts.

The minimum required is the following:

  • Contained Database User or SQL Server Login mapped to Database User
  • VIEW ANY DATABASE on server to Login
  • GRANT SELECT* on table to User
  • GRANT UPDATE** on table to User
  • GRANT INSERT** on table to User
  • GRANT DELETE** on table to User

* SELECT is required because when you click Edit Top 200 Rows, SSMS must first SELECT the top 200 rows to display them for editing.

** You only need to grant the permission for the action you want users to be able to perform.

This is the minimum requirement for a single table. To allow Edit Top 200 Rows on all tables, you can either grant the permissions to all tables individually or use a built-in role such as db_datawriter.

EDIT: As per David Browne's comment, you also need VIEW ANY DATABASE to see the DB in SSMS. By default, this is granted via membership in the Public server role during login creation, however if the Public role membership has been revoked, you'll need this explicit permission.

Related Question