How to GRANT Permission on many views? Azure SQL

azure-sql-databasepermissions

We have to grant permissions to alter 6 Views to one user.
Is there any way to do it in one query, or do I have to make one query for each different view?

I have no problem doing it one by one because there's only six of them, but if I had to do it for 100 or 200, it would became impossible.

Best Answer

If you were still developing database, you could create views within a single schema, and add that user or group of users/role , permissions to alter that schema alongside permission to create view. Such as :

GRANT ALTER ON SCHEMA::ViewSchema to ViewsEditorUser
GRANT CREATE VIEW to ViewsEditorUser

When you are all set and done, you can transfer those views from ViewSchema to dbo or some other more meaningful schema with command: 

ALTER SCHEMA NewSchema TRANSFER ViewsSchema.FirstView

If views are already created, you can either add grant alter schema (name of the schema where the views reside) and add permission to user to create a view. But be aware that since you granted a user alter schema, he has all the rights on that schema, including truncating/droping tables etc..

Last option is creating a dynamic query that will grant a user/role to alter each of these views

Update

Using dynamic query would look something like this:

     DECLARE  @ViewTable as table (ID int, ViewName varchar(50))
     DECLARE @ID int = 1,@MaxID int,@ViewName varchar(50),@Query varchar(200)    
     INSERT INTO @ViewTable
   select ROW_NUMBER() over (order by(select null)) as ID, sys.schemas.name + '.' + sys.all_views.name as name from sys.all_views
   inner join sys.schemas on all_views.schema_id = sys.schemas.schema_id
       where sys.all_views.name like '%FilterOnDesiredViews%'

    SET @MaxID = (select max(ID) from @ViewTable)

       while(@ID <= @MaxID)
       BEGIN
       SET @ViewName = (select ViewName from @ViewTable where ID = @ID)
       SET @Query = 'GRANT VIEW DEFINITION on ' +  @ViewName +' to SomeUser'
       exec (@Query)
       SET @ID = @ID + 1
        END

Note that i filtered the view names retrieval query with some silly name, you should apply your own filtering.

Related Question