There are three reasons why this probably won't be your solution:
1. I'm working with SQL Server 2005.
2. I haven't finished getting it to work.
3. Because the data access object I use opens and closes a DB
connection for each query it performs, it looks like I can't use the
Application Role approach (I don't know if that exists in 2000).
What I'm doing is creating a role that has restricted rights,
including removing access to specific tables with the intention that
no one can just execute actions on those tables, even in Query
Analyzer. All the users that will have access to the database will be
given rights through this role. I'll create stored procedures that
receive a password as a parameter. I'll store that password,
encrypted, in my application and pass it into the stored procedures so
that only the application can perform those actions when the stored
procedure validates the password. The role that I created will also
prevent access to the stored procedure for viewing and modification
(if that's necessary), but will allow executing it. Inside the stored
procedure, I'll need to perform an "Execute As" and specify a
"principal" that has rights to the table and most likely perform a
"Revert" when the procedure is done. Like I said, I haven't completed
this process yet, so there are probably holes in it. You can look at
the thread that got me going in this direction by going to
http://groups.google.com/group/micro...86aa731d8e168d,
which also has a link to using the application role (may not be
availale in 2000).
Reasons why I think the Application Role approach is more desirable:
1. The application role enforces a complex password.
2. There's a built-in procedure called sp_setapprole that takes
parameters for the application role, the application role password,
encryption type (ODBC or none), a boolean for allowing creating a
cookie for maintaining the maintaining the increased rights across
connections (I wasn't able to get that to work), and a cookie. This
ostensibly handles the setting and resetting of rights by causing all
actions performed during that connection session to be performed by
the application role.
So my approach will just be a different way of performing the same
thing and performing the rights adjustment right in each stored
procedure, allowing me to continue closing the connection after each
action. I look forward to having people help me improve my approach
or figure out how to make the Application Role approach work for me.
Let us know what ends up working for you.
On May 9, 11:45*am, acw <acwom...@yahoo .comwrote:
On a SQL Server 2000 db I would like to setup a stored procedure that
accesses couple tables and runs the extended stored procedure
xp..cmdshell. The goal is to grant users with limited privileges the
right to run the stored procedure but not the rights to directly
access either the referenced tables or the extended stored procedure.
TIA!
