HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Allow update access to only 2 tables in a database schema

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
tablesupdatedatabaseallowschemaonlyaccess

Problem

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.

Solution

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'

Code Snippets

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]
DENY UPDATE ON SchemaName.TableName3 TO [UserName]
EXEC sp_addrolemember 'db_datareader','UserName'

Context

StackExchange Database Administrators Q#56587, answer score: 9

Revisions (0)

No revisions yet.