Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 limiting user privlages-run querry only

Author  Topic 

litrainer1
Starting Member

1 Post

Posted - 2013-07-09 : 12:53:48
my users create data in excel which summarize into sql-

this is the code:

insert into spipublic.serviceattendance (as_tstamp, as_lastuser, as_se_id, as_startdate, as_reason) values ('7/5/13', 'dba', '114250', '2/2/13', 'Provided') ;

the output is one column but can be several hundred rows. The "insert" is always the same, the "values" vary by row.

i can copy the data, paste it into sql and run the query. i have server permissions, my users, who are inexpirenced and dangerous, do not.

My ideal is they they can log onto to the server and then
open to the querry pane for the corrent database (call it DATA1)
paste their excel data
run querry
exit

they can't go anywhere else, can't do anything else, just log on, paste and run.

i'm committed to having the data start as excel, and this seem like a good approach; but if you have a better or smarter way, i'm willing to try and do anything.

michael

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-09 : 17:09:27
That approach can certainly work - you can give ONLY insert permissions on spipublic.serviceattendance to the users. But, it is a less than robust and cumbersome approach. Users might insert the data more than once; they will need to go through several steps to insert the data etc.

You could have the users save the file and have them import it into the database. There are a number of ways to do this: http://support.microsoft.com/kb/321686

Another approach might be to add a button to your Excel sheet and add a macro to it which goes through the data rows and inserts them into the database. Google for examples http://stackoverflow.com/questions/3767879/insert-data-from-excel-into-sql-db

You could also have them save the file and write a powershell script to load the excel data and insert it into the tables. Here is an example: http://poshcode.org/1098
Go to Top of Page
   

- Advertisement -