Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Execute resultset

Author  Topic 

Fitione
Starting Member

3 Posts

Posted - 2013-09-24 : 09:57:54
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;

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-24 : 10:19:46
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;

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


How are you trying to execute it? The simplest would be to just copy the results of this query to a query window and execute it.

If you want to automate it, you will have to concatenate all the grant statements (separated by semi-colons) and then use dynamic sql to execute that, like shown below:
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;
Go to Top of Page

Fitione
Starting Member

3 Posts

Posted - 2013-09-24 : 10:48:49
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.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-24 : 11:16:59
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.

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.
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;
Go to Top of Page

Fitione
Starting Member

3 Posts

Posted - 2013-09-25 : 02:33:46
Hi James K,

It works like a charm.

Thanks for the help and the explanation! I learned a lot from it.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-25 : 08:17:24
You are very welcome - glad to help.
Go to Top of Page
   

- Advertisement -