Stored procedures: Prompt users for input

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.

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

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

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

mitek, what is a "clear-text" password?

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)

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

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.

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 :))

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!


 

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