SQL Server – Allow Update Access to Only 2 Tables in a Database Schema

sql serversql-server-2008-r2

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.

Best Answer

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'

Related Solutions

SQL Server – Deny Access to Information Schema

You should be able to just deny permissions on the entire sys and information_schema schema as a whole:

DENY SELECT On SCHEMA::sys To [user_name]
DENY SELECT On SCHEMA::INFORMATION_SCHEMA To [user_name]

That should basically just prevent that user from doing any selects in those two schemas.

Sql-server – How to block user access to perform database application update

There are couple of ways that you can restrict access to a database :

Using LOGON Trigger - but only temporarily (enable before upgrade and then disable it). Refer to my answer here for more details including a script.
You can shutdown IIS on the webservers so that no connections are made using the application. This is called "Applicaiton downtime"
Keep database in single user mode using (Note this will be risky as if there is any other connection to the database then you might end up waiting or refused connection.)
```
alter database databasename
set single_user with rollback immediately
```

You are better off using Option 2 as a safe and planned upgrade during your maintenance window.

EDIT:

Restricted user - only users with dbo rights on database allowed (e.g. db_owner, dbcreator, sysadmin). This means that multiple users can still be logged into the database, as long as they are DBO.

Single user - only one connection allowed i.e. first come, first served.

Due to the fact that Single user will be first come, first served -- it will be more risky in case of error or somehow your connections gets terminated.

When dealing with Logon Trigger, as @AaronBertrand pointed out, that it will not work for existing sessions, but you can over come that by first killing all the sessions and then enabling the trigger so that all the new incoming connections have to go through the trigger.

I can't think of any other way of restricting the connections to the database.

Best Answer

Related Solutions

SQL Server – Deny Access to Information Schema

Sql-server – How to block user access to perform database application update

Related Question