Solved

Restricting access to stored procedure

Posted on 2011-03-16
8
305 Views
Last Modified: 2013-11-05
Hi All,

Here is the problem (without getting into specifics).  An IT support staff runs a SQL query using SQL query analyzer, saves the output to an excel spreadsheet, which is then uploaded into a system.  Since the query invovles financial transactions, there is a possibility of manipulating this file prior to upload.  One solution I can think of is to create a stored procedure to which the user will have only execute access (and will not be able to view/edit it) and the output will be saved into a folder to which he will not have access.  Is this possible?  What kind of privileges would have to be granted?  Also, if the stored procedure needs to write an excel file on the server, will the user need to use a privileged account?

Thanks
0
Comment
Question by:ISS_Expert
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35147611
Does the system which generate output is the same system which output will be uploaded to?
If it's a "yes", then why not output result into table directly?

Anyway, for your question. Yes, you can grant execute on stored procedure to IT Support's user for them to only run it.  And SQL can write file to system regardless of user, since SQL will run under either dedicate user or under system account.
0
 
LVL 7

Expert Comment

by:mmr159
ID: 35147640
What you're looking to do is possible but...

> An IT support staff runs a SQL query using SQL query analyzer, saves the output to an excel spreadsheet, which is then uploaded into a system.

Is the sole purpose of the spreadsheet to upload this data into another system?  If so, I would seriously consider removing the middle man from the equation.

If you absolutely cannot invoke the sp from the other system or inject the data directly to it, I would wrap the sp in a bit of code that receives the result set from the sp, then inserts into the other system.
0
 
LVL 1

Author Comment

by:ISS_Expert
ID: 35149907
Thanks Joe for your reply.

mmr159 - yes, the sole purpose is to upload this data (batch posting) into another system.  The problem is that the requirement keeps changing and so the cost and time of developing a program may exceed the benefit.  Can you please suggest how to wrap the sp in a bit of code (considering that  the sp could keep changing)?

Thanks.
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 7

Expert Comment

by:mmr159
ID: 35151439
Functionally, it will still execute the stored procedure, so there is no problem with changing requirements.  You will give execute permissions to the user executing or dedicated SQL user.

The executable will take any params, either command line or a small GUI.  On "Go", the code will execute the proc, process the result set and insert it into the other system.

Now, as to writing the code... that's a pretty big question.  What language?  Platform?  What do you have experience with?

For instance, this could get you started in c#... but unless you have experience with it, compiling, etc. the learning could be a little steep.

http://www.c-sharpcorner.com/uploadfile/dclark/insoutsincs11302005072332am/insoutsincs.aspx
0
 
LVL 7

Expert Comment

by:mmr159
ID: 35152996
The hidden cost of implementing, maintaining, and chasing problems with the Excel method far outweigh the cost of writing a small program such as this.

Actually, one thing that that must be discussed is the "other" system.  What is it?  A database?  Is it SQL compliant?  If yes, then we might be discussing this program for nothing.  I assume not.  So that leaves us with how the data gets into this system?  Does it have an Excel 'import' function?  Does it have other documented methods of receiving this data?
0
 
LVL 1

Author Comment

by:ISS_Expert
ID: 35153264
Thanks a lot mmr159 for your reply.  Both systems are running SQL server.  While I agree that it the output could be directly written to the other database, I think the business wants to have the ability to view the transactions before posting (they have a maker-checker control).
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35153330
For this 2 systems,
a) Is it able to reach each other directly? (either one way, or bi-directional)
b) Do you have any server which can reach both servers?

if got one "yes", then you may able to implement semi-automatic job.
(actually, it should be automatic, but since data need to review before process, then it's turn to be semi-auto)
0
 
LVL 7

Accepted Solution

by:
mmr159 earned 500 total points
ID: 35166101
> While I agree that it the output could be directly written to the other database, I think the business wants to have the ability to view the transactions before posting (they have a maker-checker control).

But this is in direct contrast to what you're trying to accomplish...

> Since the query invovles financial transactions, there is a possibility of manipulating this file prior to upload.

So if I understand this correctly:

Pull data from DB1 via SP into temporary holding.  Allow a selected group of people to view and/or modify the temporary data.  Upload into DB2.

This does not sound like the most optimal way of doing things.  However, if that is what you want... what JoeNuvo said earlier is correct:

> Anyway, for your question. Yes, you can grant execute on stored procedure to IT Support's user for them to only run it.  And SQL can write file to system regardless of user, since SQL will run under either dedicate user or under system account.

There are a lot of write-to-csv resources out there.  Just google sql write to csv.  SQL Server will write the file directly using its credentials (i.e. Local System).
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question