|
| This post is for SQL 2000.
We are trying to implement a process where a user can turn on a role when
needed and turn off when not needed. We use this process in our Sybase
environments.
Step 1: Non-privileged use logins in.
Step 2: The user, with only the public role enabled at login, executes a
stored procedure from the master database. (the public role has execute
privs for this stored procedure). The user passes in 2 parameters, the role
name and a 1 (to turn on) or a 0 (to turn off).
Step 3: The stored procedure queries a table in different database to see if
the user is authorize to have the given role enabled.
Here is where we get our 'select permission' error. The stored procedure
in master as well as the 2nd database and table are all owned by dbo and the
'sa' account is dbo. Neither the user or the 'public' role have select
permissions granted to the lookup table.
Is there a way to have this process work without granting select privs
to either the user or the public role? There is sensitive data in this table
that we want to protect and therefore don't want to grant the select privs.
This idea works in Sybase because the calling stored procedure in master has
been compiled by 'sa'.
Thanks for any help that you can give.
Joe D.
|
|