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.
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; |
|
|
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 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. |
|
|
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 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.DECLARE @sql NVARCHAR(MAX);SELECT @sql = xmlColFROM ( 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; |
|
|
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. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-25 : 08:17:24
|
You are very welcome - glad to help. |
|
|
|
|
|
|
|