July 23, 2012 at 1:57 pm
Hi,
I have a situation to grant view definition access only on functions and views in XXX database to a login.
Do we have a any direct TSQL command or i need to write some code to achieve this? Any suggestions please
Thanks
Seshu
July 23, 2012 at 2:07 pm
The grant command is what you are looking for
http://msdn.microsoft.com/en-us/library/ms187965.aspx
i may look at a custom database role so you do not have to grant every thing to multiple users. only to the custom database role then assign users to that.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
July 23, 2012 at 2:12 pm
To add to Capn's response, the following script can be tweaked to generate the sql you need.
declare @login VARCHAR(30) = 'UserID'
SELECT 'Grant View Definition ON ' + schema_name(schema_id) + '.' + [name] + ' TO ' + '[' + REPLACE(REPLACE (@login, '[', ''), ']', '') + ']'
FROM sys.all_objects s
WHERE type IN ('P', 'V', 'FN', 'TR', 'IF', 'TF', 'U')
/*
P - Stored Procedure
V - View
FN - SQL scalar-function
TR - Trigger
IF - SQL inlined table-valued function
TF - SQL table-valued function
U - Table (user-defined)
*/
AND is_ms_shipped = 0
ORDER BY s.type, s.name
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 23, 2012 at 2:43 pm
Thanks, Sean.
From MSDN, what i could see is to grant permissions to all the views & functions separately. So, your query would help me generating a batch to execute the same, right...
So, no other way we can grant permissions to all views or functions in a single command?
July 23, 2012 at 2:53 pm
seshu2all (7/23/2012)
Thanks, Sean.From MSDN, what i could see is to grant permissions to all the views & functions separately. So, your query would help me generating a batch to execute the same, right...
So, no other way we can grant permissions to all views or functions in a single command?
Yes this would help you generate the queries. The still need to be executed.
There is now way that I know of to make this any easier. You want pretty granular permissions so you will have to set them at the object level.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 29, 2016 at 12:21 pm
Sean Lange (7/23/2012)
To add to Capn's response, the following script can be tweaked to generate the sql you need.
declare @login VARCHAR(30) = 'UserID'
SELECT 'Grant View Definition ON ' + schema_name(schema_id) + '.' + [name] + ' TO ' + '[' + REPLACE(REPLACE (@login, '[', ''), ']', '') + ']'
FROM sys.all_objects s
WHERE type IN ('P', 'V', 'FN', 'TR', 'IF', 'TF', 'U')
/*
P - Stored Procedure
V - View
FN - SQL scalar-function
TR - Trigger
IF - SQL inlined table-valued function
TF - SQL table-valued function
U - Table (user-defined)
*/
AND is_ms_shipped = 0
ORDER BY s.type, s.name
+1
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy