Allow read-only user to execute a user funcition

Question

Allow read-only user to execute a user funcition

n.heyen

SSCrazy

Points: 2849
More actions
July 24, 2012 at 2:07 pm

#259787

First off, my apologies if this is really basic but I've never done it and can't figure it out.
I have a function in one database (created be me, a member of the sysadmin group) that needs to be called by a read-only user that doesn't have permissions in that database. (The function strips off leading and trailing white space from a string, as seen in Pinal Dave's blog - http://blog.sqlauthority.com/2008/10/10/sql-server-2008-enhenced-trim-function-remove-trailing-spaces-leading-spaces-white-space-tabs-carriage-returns-line-feeds/[/url] )
I tried altering the function to add the WITH EXECUTE AS OWNER clause but get a permission denied error for the function.
I would prefer to not add the read-only user to the database that contains the function. I know(or at least I think I know) I can add the read-only user to the table and grant execute permissions to the function but isn't there another way?
Yes, I struggle with permissions. We don't do development in-house so I am usually limited to what the software suppliers insist on setting up. I'm trying to learn but learning T-SQL from BOL is like learning English from the Oxford dictionary. Everything you need is there but really hard to put it all together without some help...
Thanks in advance,
Norman

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

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

CapnHector SSCoach Points: 16143 More actions · Answer 1

in order to grant permissions to an object the server user must exist as a user in the database (unless when trying the same thing i missed something). the other option instead of adding the server user as a user in the database with the function is to create the function in the users database and grant them rights on the "new" function.

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]

n.heyen SSCrazy Points: 2849 More actions · Answer 2

Thank you Cap'n Hector,

I was sort of afraid that was the answer. I can create a db user and grant permissions. That works, I was just hoping there was another way and still keep it as simple and unobtrusive as possible.

I was trying to see if there was a way to create a database of user functions, etc. separate from the user database and allow the read-only user to execute them. It just seems there should be an alternative way to do this without having as much maintenance as creating all the users and granting permissions on the functions to each of the users.

Thanks for the response,

Norman

CapnHector SSCoach Points: 16143 More actions · Answer 3

n.heyen (7/24/2012)
Thank you Cap'n Hector,
I was sort of afraid that was the answer. I can create a db user and grant permissions. That works, I was just hoping there was another way and still keep it as simple and unobtrusive as possible.
I was trying to see if there was a way to create a database of user functions, etc. separate from the user database and allow the read-only user to execute them. It just seems there should be an alternative way to do this without having as much maintenance as creating all the users and granting permissions on the functions to each of the users.
Thanks for the response,
Norman

you can create a custom database role. create your functions database, create all your functions, create a custom database role and grant execute to the functions you want users to be able to execute, then add users to the custom database role.

Relevant links

CREATE ROLE http://msdn.microsoft.com/en-us/library/ms187936.aspx

GRANT http://msdn.microsoft.com/en-us/library/ms187965.aspx you can grant permissions to a custom role after its made.

Add users to role http://msdn.microsoft.com/en-us/library/ms187750.aspx

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]

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

I haven't done it in a while but I believe you need to grant SELECT on the function along with EXECUTE AS OWNER. I could be wrong, though.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

n.heyen SSCrazy Points: 2849 More actions · Answer 5

Thanks again Cap'n Hector,

I don't think I can group every login I need into a group but I'll look at this. I just thought it would be a decent way to create a library of useful functions for the report developers to use.

But I did create the use and granted permission to execute on the database, seems to work.

Thanks for the help, at least I can stop looking for another solution.

Norman

n.heyen SSCrazy Points: 2849 More actions · Answer 6

Thanks Jeff,

I'll see if I can make that work but for now, I'm re-thinking what I wanted to do and see if there is another approach.

Thanks again,

Norman