CODEWITHSUNDEEP
sql-serversql-grantuser-roles
Sreedhar Danturthi
Sreedhar Danturthi

Reputation: 7601

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

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

Upvotes: 23

Views: 82780

Answers (2)

Sreedhar Danturthi
Sreedhar Danturthi

Reputation: 7601

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

Upvotes: 47

Karthik Surianarayanan
Karthik Surianarayanan

Reputation: 626

Granting access through user name is specific only for that user.

But granting access through role is applicable to all the users who belong to that role. Role is used for assigning permissions to a group of users.

Upvotes: 2

Related Questions