March 26, 2010 at 6:03 am
Hi all,
Using SQL 2000 sp4 on Win2K3, Windows authentication.
Here's the setup: I've created a table function which exists in each of my user dbs. It's called from within a number of stored procedures in each of the dbs. When they call the sprocs, my users are connected to the DB in which it's being called.
The permissions on the function itself is public, as well as two db roles, to one of which these users are assigned.
It's a simple select statement, the logic is as follows:
IF DB_NAME() IN (non-execptional DBs)
select column
from dbo.table
where columnA = @arg
ELSE IF DB_NAME = (exception DB)
select column
from exceptiondb.dbo.table
where columnB = @arg
RETURN
Now the problem is that, some of the users calling the function in other DBs get a message saying "User xxx doesn't have access to (execeptiondb)". Which is true.
If I rewrite the ELSE IF to omit the exceptiondb name, the function doesn't compile, since columnB does not exist in the non-exceptional dbs.
My immediate solution has been to call the function, then do a SETUSER to their login, and call it again. When they then call it via the application, it works.
So, my guess is that it's a compilation thing. However, the server's been running for 2 months, and the problem only began manifesting itself two users today -- on Friday it worked for one, but not the other. For all the others (who don't have access to exceptiondb), it's still working AFAIK.
My medium-term solution is to create a nightly job to call the function in each DB, so it compiles.
I would like a real / long-term solution. What can be done -- short of maintaining two versions of the function?
Thanks,
Paul
March 26, 2010 at 1:41 pm
If I understand this correctly, you're saying that it's failing even when you're not attempting to select from the table that you don't have rights to.
I tried to duplicate this and couldn't easily do so, but one thing to possibly try is to create 2 more functions, one for each select and put the code in those, in the hopes that it doesn't compile the select and thus doesn't throw the error.
March 29, 2010 at 6:15 am
Hi Seth,
Thanks for having a look. If I understand your proposal, it's to break down the single into two. That means I'm maintaining two versions of the function, while the point of this exercise was to only have a single version.
Moreover, it still doesn't explain why it works for some all of the time and others some of the time.
Further developments: it appears that when a third user accesses it, then the users for whom it was previously failing are suddenly able to run it without error. IMHO this suggests something about cached execution plans.
I'll keep plugging away.
March 29, 2010 at 9:49 am
Ordinarily, I'd have said that Dynamic SQL would likely have fixed the issue, but you can't use that within your function. I think you're on the right track with the cached plan credentials, I just can't remember specifically what the issue was.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply