?
Solved

Stored procedures: Prompt users for input

Posted on 1998-08-11
12
Medium Priority
?
470 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 150 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

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…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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…
Suggested Courses

777 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