Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
you mean granting select permision on the schema and granting update permission on the 2/3 tables? –  Edward Dortland Jan 11 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 at 11:20

3 Answers 3

up vote 3 down vote accepted

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'
share|improve this answer
    
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 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. –  Aaron Bertrand Jan 12 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? –  Kenneth Fisher Jan 12 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. –  Aaron Bertrand Jan 12 at 4:33

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)

share|improve this answer

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.

share|improve this answer
    
Yes Absoulutely correct. –  Linu1988 Jan 11 at 19:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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