1

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.

1

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.

  • Thank you. The database is already created. We are Azure Admins so SQL is kind of fuzzy, but the client asked us to give him ALTER on 6 Views out of 30. All those 6 had a blabla_identicalending name. So first we thought we could do something like GRANT ALTER ON dbo.*identicalending* But we weren't sure * was going to work so we didn't test it. So we granted permission on each one of the 6 views one by one. How would the dynamic query would look like if give permissions to 25 out of 30 Views in the same folder? Of course, no need to type it for me but I need a little guidance. Thank you – Dante Nahuel Ciai Jul 13 '17 at 13:31
  • I had to update the post, so i could provide you the query. Hope it helps! – S4V1N Jul 13 '17 at 18:10

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.