Question

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 VB.net and SQL Server Express


Sponsored



Replies

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


Jeff07,

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]
GO
ALTER ROLE [db_datareader] ADD MEMBER [yourLogin]
GO

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

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

Read the links below to help you:

https://msdn.microsoft.com/en-us/library/ms189121.aspx

https://msdn.microsoft.com/en-us/library/ms173724(v=sql.120).aspx


Felipe Lauffer MCSA: SQL Server | MCP