Solved

Stored procedures: Prompt users for input

Posted on 1998-08-11
12
462 Views
Last Modified: 2011-04-14
Is there a way to prompt users for input from a
stored procedure using Transact-SQL?
0
Comment
Question by:vd
[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
  • 8
  • 4
12 Comments
 
LVL 4

Expert Comment

by:mitek
ID: 1089521
Why would you need something like that ?
There is no way to do that. On the other hand, i'm just curious, what is the reason why you need this.

TSQL procedures are not supposed to be used in that way. The user input is usually collected somplace else and passed to TSQL SP for processing.

0
 

Author Comment

by:vd
ID: 1089522
Mitek,

This is a good point. I wanted to provide an easy way for a  non-sa user to issue certain SA commands without giving him
the SA password! Because he uses ISQL he would like this approach rather than me build a program interface....
0
 
LVL 4

Expert Comment

by:mitek
ID: 1089523
I see ...
One way to do that would be create a special account in SQL Server and explicitly grant it the permissions to run only the commands he heeds to run.

then, write a batch script (better yet, a perl script) that will contain the isql call, like

isql -S SERV -U special -P x123

with clear-text password.
the account should be as restricted as possible, giving only the minimum level of necessary permissions to run certain commands on certain set of objects

the perl script, on the other hand, should be also assigned permissions to be readable/executable only by this specific user.

that's the way i would do it ...
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 4

Expert Comment

by:mitek
ID: 1089524
Or, if you want to get really fancy, give this account one and only one permission -- to execute a specific SP, that will take the command to be executed as one of its parameters, validate, that it's ok and execute it on the behalf of the user.
But again, the SP will execute these commands with SA authority, so this SP has to be written very carefully.
It should also check the username of the user who runs it and deny anyone who is not on the list of valid users to run it.

Just ideas ...

0
 
LVL 4

Accepted Solution

by:
mitek earned 50 total points
ID: 1089525
So, here is the way I would do that:

a) create a special user account. revoke most of its default permissions that it doesn't need and grant it a very specific (narrow) set of permissions.

b) write a perl script that collects the required input, and runs the appropriate query based on that input.

c) make this script non-modifyable by anyone and readable/executable by a specific user who needs to run it. it shouldn't be readable by anyone else (except Administrators, of course)

d) if GRANT statement can't give you the granularity you need, consider writing a store procedure that will verify the user and check (possibly, based on some kind of custom "ACL" table) if the operation requested is allowed. But, in 90% of scenarios, it shouldn't be necessary, and built-in SQL Server security should do just fine.


0
 

Author Comment

by:vd
ID: 1089526
mitek, what is a "clear-text" password?
0
 
LVL 4

Expert Comment

by:mitek
ID: 1089527
well, i mean, a perl script will contain a call like this one

$result = `isql -S SERV -U special -P passwd -Q "$action"`;

clear-text password means that password is not encrypted,
and potentially anyone can sniff it by looking at the script code

or, better yet, do something like

$result = `isql -S SERV -E -U special -Q "$action"`;

this way, you won't need to put a password in the script file, since option -E employs "trusted connection", meaning it'll use the NT password that was entered by user at the login prompt !

(run isql /? to find out all other options needed for the command to run correctly. there are much more isql options you'll need to put into this call)


0
 

Author Comment

by:vd
ID: 1089528
I see...we don't have Perl script. I like your idea of a SP
that executes with SA authority...however, if a non-SA ID attempts to execute a SP which contains, lets say, an sp_addlogin command,an error occurs indicating one has to be a database owner
or an SA to execute this command.....  
0
 
LVL 4

Expert Comment

by:mitek
ID: 1089529
well, perl script was just an example. can be dos batch script, or something like that.

the thing is ... i'm almost sure that an SP owned by SA will behave like a unix suid script, meaning that all the commands within it will be executed with SA authority. i noticed that a while ago ... you should definetely test it.

tell me what you found out.
0
 
LVL 4

Expert Comment

by:mitek
ID: 1089530
A really sick way of doing what you need to do would be create an SP which will run in SQL Executive, say, every 30 minutes (well, of course, it will be executing with SA authority !)

it will be watching for a special table with flags (and possibly SQL statements, too)

SP does a select against it every 30 min or so. if flags are not set, it does nothing. otherwise, it will execute an SQL statement corresponding to the flag (possibly, but not necessarily, reading it from the same table)

there will be another SP that will modify this table's flag states, an it can be executed by a user.

the idea is, user will not directly run the SA commands. it will be done on his behalf by an SQL Executive SP, at regular time intervals.

but i think it's just paranoic, don't pay attention to this nonsense :))


0
 

Author Comment

by:vd
ID: 1089531
I tried creating a SP from SA id; if it contains SA type
commands or SP calls, it will not work for a non SA id!


 
0
 
LVL 4

Expert Comment

by:mitek
ID: 1089532
then -- try the sick solution. it's actually not that sick, it's just not obvious and will be harder to implement. but, if implemented correctly, it will surely be one of the safest methods that will do the trick.

notice, that prompting for user input from an SP (if such possibility existed) wouldn't even help, since this SP wouldn't be able to run statements with an SA authority anyway ...

0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

733 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