EXECUTE permission denied on user defined function

Question

EXECUTE permission denied on user defined function

MattieNH

SSCertifiable

Points: 7463
More actions
July 26, 2005 at 8:49 am

#86961

I have to report against an application that contains four databases. We have a fifth database (Reporting) that contains only our objects, including the stored procedure I have written that refers to tables in the four application databases. All five databases are on the same server.
The user that runs this stored procedure has a server login (AppReporter) and public access (database role) to the four application databases. In the fifth (Reporting) database, AppReporter has public access plus the rights given to a database role called AppReports.
AppReports is a role that exists in the Reporting database, and all it has is execute access to the stored procedure I have written. Right now, the sole user in the AppReports role is AppReporter. AppReporter has no rights other than those conferred by the public and AppReports roles.
The problem I'm having is that my stored procedure uses a user function (Enroll.dbo.fnFullName) that exists in one of the four application databases. When I run the stored procedure in Query Analyzer, I get the following error
EXECUTE permission denied on object 'fnFullName', database 'Enroll', owner 'dbo'.
The rules I'm operating under are these:
1. Everything has to be done through roles.
2. No changes should be made to the application databases, because the changes risk being removed when new releases are installed.
What I've done for a work-around is to copy fnFullName into my reporting database, but I'm not 100% happy with that solution.
So my question is: given this setup, is there a way I can have my stored procedure use Enroll.dbo.fnFullName?
Thanks in advance,
Mattie

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

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

Create a database role in the Enroll database. Call it the same, AppReports, if you'd like. Make the user a member of this database role. Grant the database role EXECUTE rights on the fnFullName function.

This is the most secure way of handling it. You can also use Cross Database Ownership Chaining if the user owners (dbo in your case) map to the same SQL Server login (and with dbo that means the login that owns each database would have to be the same). But that opens up a lot of implications on the security front and is usually a method of last resort.

K. Brian Kelley
@kbriankelley

MattieNH SSCertifiable Points: 7463 More actions · Answer 2

Brian,

Thanks so much. I'm guessing I'll have to stick with the copy method, because the powers that be will most likely tell me your solution violates stricture #2:

No changes should be made to the application databases, because the changes risk being removed when new releases are installed.

I have to say it seems a little odd that if I give a user access to a stored procedure, they auto-magically have the required access to the tables, but the same doesn't hold true for user functions.

Anyway, thanks again. You saved me a ton of time looking for a solution.

Mattie

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

They don't automatically have access to the tables unless cross database ownership chaining is on... but in that case the function would also work. Does the public role have access to the tables in the Enroll database but not the function itself?

K. Brian Kelley
@kbriankelley

MattieNH SSCertifiable Points: 7463 More actions · Answer 4

That's it: public has select access on all tables in the Enroll database, but no execute access. I'm guessing that's so their stored procedures can have access across databases.

When I run

EXEC sp_configure 'Cross DB Ownership Chaining'

it returns 0, so it looks like they're not using cross database ownership chaining.

So I've removed my copy of fnFormatName from the reporting database, and given AppReporter execute access to it in the Enroll database. That requires an application database change, but so does giving AppReporter access to the database to begin with.

Thanks again.