SQL Server – How to Prevent Data Modification Through an Exposed View

Securitysql serverview

I have a database: DB 1

In a different database DB 2, I want to create a bunch of views that reference data in DB 1. These views will be relatively complex, with lots of joins, aggregations, sub-queries, etc.

I don't want users of DB 2 to be able to modify the data in DB 1. i.e. I don't want users to be able to be able to execute INSERT, UPDATE, DELETE queries on the view.

This blog post: http://sqlhint.com/sqlserver/views/creating-readonly-views

suggests I can use an INSTEAD clause. I would prefer to just dissalow certain query clauses on the view itself except for a particular user. Is this possible?

Best Answer

This is handled through the view's and table's security. Grant only SELECT permission to the view and the underlying tables.

Without write access, INSERTs, UPDATEs and DELETEs through the view will fail.

If the users have access through a group, and you want the group to still have access but not these specific users, you could use the DENY permission since DENY has precedence over GRANT.

Related Question