Post reply

grant exec on function for all dbs

  • June 16, 2008 at 9:18 am

    #121972

    Hello Everyone,

    windows 2003 enterpr, sql 2005 sp2

    we have 30 dbs, and every one of them have the same Scalar-valued Function.

    Also, a windows auth login account that have to have a right to exec that function within every database

    Question: i can make this function global by creating it in a master db or i can go to every single database and grant perm

    to exec that function for this windows login.....

    is there any other way to grant function exec permission for this specific user on the server level?

    so it can be done once, and not for every single database that we have

    thanks

  • June 16, 2008 at 11:10 am

    #829360

    Not that I know of.

    I'd put in every database and use a script to create/alter/grant permissions.

  • June 16, 2008 at 11:19 am

    #829368

    thank you, Sir

  • June 16, 2008 at 11:21 am

    #829370

    slava davidchuk (6/16/2008)

    is there any other way to grant function exec permission for this specific user on the server level?

    so it can be done once, and not for every single database that we have

    Put the function in one database, it can be [Master] or any other, lets call it [Common]. Give this user's Login a user mapping [Common] and then in the [Common] DB grant that user access to the function.

    Now that Login will be able to access the function from any database. Of course that will have to specifically call it in that database:

    Select @var = Common.dbo.fnSomeFunction(...)

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • June 16, 2008 at 12:03 pm

    #829394

    wow, so simple and so great!!!!!!!!

    nice to be smart... 🙂

    Thank you

  • June 16, 2008 at 12:24 pm

    #829403

    thanks, slava.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply