Solved

Stored procedures: Prompt users for input

Posted on 1998-08-11
12
456 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
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
tempdb log keep growing 7 33
Binding error when running a view SQL Server 3 26
TSQL convert date to string 4 34
install report service in sccm2012 3 18
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 …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

840 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