473,320 Members | 1,910 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Limiting table access using stored procedure.

acw
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!
Jun 27 '08 #1
2 4056
JT
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!
Jun 27 '08 #2
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.
If you tables are owned by 'dbo', you can accomplish this with
cross-database chaining as follows.

1) ensure your user database is owned by 'sa': EXEC sp_changedbowner 'sa'

2) enable the 'db chaining': EXEC sp_dboption 'MyDatabase', 'db chaining',
true

3) Configure the SQL Agent proxy account by unchecking the 'only users with
sysadmin ...' checkbox in Enterprise Manager under SQL Server
Agent-->Properties-->Job System. Make sure the proxy account has the
Windows permissions needed by the task xp_cmdshell performs.

Importantly, you should enable cross-database chaining in an sa-owned
database when only sysadmin role members can create dbo-owned objects. See
the Books Online for more information.
--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"acw" <ac******@yahoo.comwrote in message
news:a4**********************************@x35g2000 hsb.googlegroups.com...
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.
Jun 27 '08 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Eric Martin | last post by:
Hello, Does anyone know of a way to loop thru a SQL table using code in a stored procedure? I need to go thru each record in a small table and build a string using values from the fields...
4
by: Not Me | last post by:
Hi, I have a stored procedure, that works perfectly when run from the query analyser, however if I run it through access vba, (using exec) I get a runtime error 208: invalid object name...
4
by: Robin Tucker | last post by:
Hi, I'm trying to determine with my program whether or not a given database supports a given feature set. To do this I'm querying for certain stored procedures in the sysobjects table and if...
1
by: Lauren Quantrell | last post by:
I have read the newsgroups and see this is a common issue but I saw no resolution for it: I have an Access2K frotn end and SQL Server 2K backend. In access, I create a temp table using code in a...
1
by: BigD | last post by:
This all centers around an Access Data Project I have a stored procedure that aggregates events stored in a table based on intervals I specify. I have a form that supplies parameters for the...
8
by: David | last post by:
Hi, Could someone please xplain how to add a field to an existing SQL table in VB.Net I have added the field in the Server Explorer and it shows up when I reload the program but I cannot...
3
by: Roger Withnell | last post by:
I have a framed website. I plan to include in default.asp a routine to check if the annual subscription is due from the records in the People table. If so, then the routine would send emails...
6
by: Peter Neumaier | last post by:
Hi, I am trying to select some data through a stored procedure and would like to store the result in a local access table. Is that possible? Can somebody provide an example? Thanks&regards!...
14
by: jehugaleahsa | last post by:
Hello: I am working with Oracle .NET Stored Procedures. I would like to know how to return the results of a SELECT statement. I have tried returning a OracleRefCursor and a DataTable, but...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.