patternsqlMinor
Allow update access to only 2 tables in a database schema
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.
The Database runs on SQL Server 2008 R2. Kindly guide me if possible.
Solution
Here is an example script
And please remember that unless the user is some level of administrator,
Then nothing you can do will grant them update access until you REVOKE the DENY.
You could add the user to
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.