How to grant view definition access only on Views & Functions in SQL Server 2K8R2

  • 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

  • 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 faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    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]

  • 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/

  • 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?

  • 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/

  • 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