1

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?

Improve this question
1
  • 2
    Perhaps you could just provide access with GRANT SELECT against the view.
    – Randolph West
    Commented Dec 8, 2016 at 19:25

1 Answer 1

Reset to default
8

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.

Improve this answer
10
  • 2
    @ZachSmith, but who needs those update permissions on the tables? And who needs the access denied?
    – Tara Kizer
    Commented Dec 8, 2016 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
    Commented Dec 8, 2016 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
    Commented Dec 8, 2016 at 20:18
  • 2
    @MguerraTorres, you could do that, but I just grant the needed permissions and then use deny as necessary.
    – Tara Kizer
    Commented Dec 8, 2016 at 21:49
  • 2
    @MguerraTorres I'm not sure, but I don't think that's possible.
    – Tara Kizer
    Commented Dec 8, 2016 at 22:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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