Read only access to SQL database for only some tables.

Category: sql server dataaccess


Jeff07 on Tue, 19 Jan 2016 18:38:00

I want a user to have read only access to all data tables except one.  How do I accomplish this in SQL Server 2014.

Any help would be appreciated.

ISV using and SQL Server Express


FLauffer on Tue, 19 Jan 2016 18:55:19


You can use the native database role db_datareader to grant read access to all tables and DENY SELECT operations in the table that you want to deny access.

You can grant the db_datareader role with the command below:

USE [yourDatabase]
ALTER ROLE [db_datareader] ADD MEMBER [yourLogin]

You can DENY SELECT permission from a table with the command below:

USE [yourDatabase]
DENY SELECT ON [yourSchema].[yourTable] TO [yourLogin]

Read the links below to help you:

Felipe Lauffer MCSA: SQL Server | MCP