How can I grant only READ access to a Single table in Sql Server Database

Sree picture Sree · Feb 7, 2014 · Viewed 60k times · Source

I want to provide only READ access to a single table in SQL Server Database for a given user - xyz

Have gone through these questions:

How do I grant read access for a user to a database in SQL Server?

Granting a SQL Server Login Access to a Database - SQL Server

best way to grant read only access to 2 tables in SQL Server 2005?

But it raises some fundamental questions for me, what is the difference in giving the access through role and user name?

Kindly provide a efficient way to do this

Answer

Sree picture Sree · Feb 7, 2014

I have gotten around this problem in this manner:

CREATE LOGIN XYZ
WITH PASSWORD = 'PASSWORD'

After the login for XYZ is created, then create a user for the above login created

CREATE USER xyz FOR LOGIN xyz

Then grant the select, update permission, in my case it is just select on a particular table

GRANT SELECT ON DBNAME.TABLE_NAME TO USERNAME

The sources I have referred for this are

  1. http://technet.microsoft.com/en-us/library/aa337545.aspx (refer the bottom code part titled create a database user)

  2. http://social.msdn.microsoft.com/Forums/sqlserver/en-US/959f9307-0494-4883-9d17-fad684705864/grant-select-permission-on-a-table?forum=sqldatabaseengine