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?

  • 2
    Perhaps you could just provide access with GRANT SELECT against the view. – Randolph West Dec 8 '16 at 19:25

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.

  • 2
    @ZachSmith, but who needs those update permissions on the tables? And who needs the access denied? – Tara Kizer Dec 8 '16 at 19:52
  • 1
    I want the tables in DB 1 to be editable only when accessed as that database (with a user who has permissions on that database). I want views to be select only for every user on DB 2 (I.e. no UPDATE, INSERT, DELETE, etc). However the view itself needs to be alterable by the same users that cannot update data through that view – Zach Smith Dec 8 '16 at 19:56
  • 2
    You could try a trigger where it will rollback with transaction if it's initiated from a remote database. I don't have the code for that though. – Tara Kizer Dec 8 '16 at 20:18
  • 2
    @MguerraTorres, you could do that, but I just grant the needed permissions and then use deny as necessary. – Tara Kizer Dec 8 '16 at 21:49
  • 2
    @MguerraTorres I'm not sure, but I don't think that's possible. – Tara Kizer Dec 8 '16 at 22:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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