Sql-server – how can I add user who can read all tables in database and have permission to alter only one table

sql server

I am creating a database using SQL server express 2016 for a college project and
need to learn how to add a user whom can have access to all tables, however, only be able to alter one of the tables, update, delete, etc… I'm using sql server
express 2016 management studio

Best Answer

Add the user to the db_datareader role within the database

sp_addrolemember @rolename = 'db_datareader' @membername '<USERNAME>'

Give the user permission to alter that single table

grant alter on [<TABLE>] to [<USER>]

This will give the permissions you want. You could also create a specific database role with the correct permissions, or grant the user permissions on the schema so you should check the msdn article suggested by @randolph-west