Post reply

Database access rights

  • March 22, 2010 at 12:16 pm

    #222169

    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 for one of two users today -- on Friday it worked for one, but not the other.

    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 22, 2010 at 12:24 pm

    #1137766

    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 for one of two users today -- on Friday it worked for one, but not the other.

    should read

    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 for both users today -- on Friday it worked for one, but not the other.

Viewing 2 posts - 1 through 1 (of 1 total)

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