SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 limiting user privlages-run querry only
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

litrainer1
Starting Member

1 Posts

Posted - 07/09/2013 :  12:53:48  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 07/09/2013 :  17:09:27  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000