quote:

---

Originally posted by Fitione

Hi all,

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.

Any ideas?

------------------------------------------------------------------------

use [AdventureWorksDW2008R2]

SELECT

'GRANT SELECT ON [' + SCHEMA_NAME(Schema_id) + '].[' + name + '] TO [DOMAIN\GROUP]'

FROM sys.views;

-------------------------------------------------------------------------

---

DECLARE @sql NVARCHAR(MAX);
SELECT @sql = 
'GRANT SELECT ON [' + SCHEMA_NAME(Schema_id) + '].[' + name + '] TO [DOMAIN\GROUP]' + ';'
FROM sys.views FOR XML PATH('');
EXEC sp_executesql @sql;

quote:

---

Originally posted by Fitione

Hi James K,

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 3
The 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.

---

DECLARE @sql NVARCHAR(MAX);
SELECT  @sql = xmlCol
FROM    ( SELECT    'GRANT SELECT ON [' + SCHEMA_NAME(Schema_id) + '].['
                    + name + '] TO [DOMAIN\GROUP]' + ';'
          FROM      sys.views FOR XML PATH('')
        ) s ( xmlCol );
SELECT @sql;
--EXEC sp_executesql @sql;