473,221 Members | 1,967 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,221 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 4046
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...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...

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.