May 4, 2012 at 4:00 pm
Hello,
I have searched for an answer, but have not found exactly what I need. I've created a new database that currently has no views built. Since the developer will be creating views later, I want to GRANT SELECT to the appuser now so that the appuser can SELECT the views immediately after they are built without me having to grant SELECT to the views as they are built. I also do not want the appuser to SELECT from the tables. How do I accomplish this?
This grants SELECT to all tables and views, does it not?
use [database]
GO
GRANT SELECT TO [appuser]
GO
May 4, 2012 at 4:12 pm
You want to use a DDL trigger. DDL trigger looks for view creation and automatically grants the appropriate permission.
K. Brian Kelley
@kbriankelley
May 4, 2012 at 5:12 pm
Or, you could create a new schema to place your views into and have it owned by the same entity as your table's schema. This would allow you to grant the user SELECT on the new view-schema and when new objects are created they would implicitly have SELECT permissions.
Loosely:
1. create new schema named MyViewSchema with same owner as schema that contains your tables
2. GRANT SELECT ON SCHEMA::MyViewSchema TO [appuser]
3. Create all new views you want appuser to have access to in schema MyViewSchema
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply