SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Execute resultset
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Fitione
Starting Member

Netherlands
3 Posts

Posted - 09/24/2013 :  09:57:54  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 09/24/2013 :  10:19:46  Show Profile  Reply with Quote
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

Netherlands
3 Posts

Posted - 09/24/2013 :  10:48:49  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 09/24/2013 :  11:16:59  Show Profile  Reply with Quote
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

Netherlands
3 Posts

Posted - 09/25/2013 :  02:33:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 09/25/2013 :  08:17:24  Show Profile  Reply with Quote
You are very welcome - glad to help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000