I'm trying to grant a user group select acces to all the views in a database. I already made a query which creates a resultset whit the SQL Syntax I (displayed below) need but it seems to be impossible to get this result set executed after creation.

I'm trying to grant a user group select acces to all the views in a database. I already made a query which creates a resultset whit the SQL Syntax I (displayed below) need but it seems to be impossible to get this result set executed after creation.

Thanks for the reply! There are over 100 views so I would like to automate it. Your code looks good but I have no experience with it. So as I don't know why you add FOR XML PATH('') I also don't know why it returns the error

Msg 6819, Level 16, State 3, Line 3The FOR XML clause is not allowed in a ASSIGNMENT statement.

When I remove FOR XML PATH('') it returns that the command(s) completed successfully. Never the less the security group is only granted the select permission to one of the 7 views in the Adventureworks Database.

Thanks for the reply! There are over 100 views so I would like to automate it. Your code looks good but I have no experience with it. So as I don't know why you add FOR XML PATH('') I also don't know why it returns the error

Msg 6819, Level 16, State 3, Line 3The FOR XML clause is not allowed in a ASSIGNMENT statement.

When I remove FOR XML PATH('') it returns that the command(s) completed successfully. Never the less the security group is only granted the select permission to one of the 7 views in the Adventureworks Database.

My mistake - which I have fixed below.

The for xml path concatenates all the grant view statements into one semi-colon separated string. You can see what it does if you when you run the query below. It selects the resulting sql string (rather than execute it) so you can examine it. Once you are happy with it, uncomment the last line and run it.