6

I would like to have a login which is capable of accessing a specific schema but should be having only update access to 2/3 tables. Is it possible?

The Database runs on SQL Server 2008 R2. Kindly guide me if possible.

2
  • you mean granting select permision on the schema and granting update permission on the 2/3 tables? Jan 11, 2014 at 10:06
  • Yes, and i did archive that using Grand, found one answer and tested with it. Do you have any suggestion or advice on the implementation?
    – Linu1988
    Jan 11, 2014 at 11:20

3 Answers 3

9

Here is an example script

CREATE USER [UserName] FROM LOGIN [LoginName]

-- Grant SELECT on the schema
GRANT SELECT ON SCHEMA::SchemaName TO [UserName]
-- Grant UPDATE on the individual tables
GRANT UPDATE ON SchemaName.TableName1 TO [UserName]
GRANT UPDATE ON SchemaName.TableName2 TO [UserName]

And please remember that unless the user is some level of administrator, db_owner for example, any DENYs that you do will override any form of grant. If you do this:

DENY UPDATE ON SchemaName.TableName3 TO [UserName]

Then nothing you can do will grant them update access until you REVOKE the DENY. You could add the user to db_datareader to give them the SELECT permissions but that will give them SELECT permissions to all schemas not just one. It just depends on what you are looking for. That command is like this:

EXEC sp_addrolemember 'db_datareader','UserName'
4
  • Yes correctly said i have given them datareader access which okay, but wanted to give them update access to only 2 tables. This user will be used on a dashoboard, wanted to make sure they dont get access to any other table.
    – Linu1988
    Jan 11, 2014 at 19:08
  • Your GRANT UPDATE commands should be ON schema.TableName not ON OBJECT::TableName. With just the latter, SQL Server won't know which schema you mean, and it would be extremely confusing anyway if TableName existed in more than one schema. Jan 12, 2014 at 2:31
  • @AaronBertrand Good point and I've changed the script. Is there any real difference between including the OBJECT:: or not? ie ON schema.TableName vs OBJECT::schema.TableName? Jan 12, 2014 at 2:57
  • Including the OBJECT:: prefix seems superfluous, and I don't know of any situation where it changes the behavior in any way at all. Jan 12, 2014 at 4:33
3

Besides using the script (which is preferred in most cases), you can do the same in SQL Server Management Studio:

  1. Right-click the table you want to grant/deny permissions to
  2. Select Properties

enter image description here

3 Open the Permissions tab

4 Click Search to find the user you want to grant permissions to

enter image description here

5 Select the specific permission and check the Grant box enter image description here

GRANT (Transact-SQL)

0

Pretty much what Edward Dortland said. If I understand what you're wanting, you probably want to create the user, add them to the datareaders role on that database, and manually give them update on the tables you want them to update.

1
  • Yes Absoulutely correct.
    – Linu1988
    Jan 11, 2014 at 19:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.