December 6, 2006 at 2:57 am
Hi,
I have used the code of the article http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=763
to generate an excel sheet with invoice data.
When the user pusches a button in the application it will trigger a stored procedure to show the invoice on screen. I just added a call within this sp to another sp that contains the cose from the article mentioned above.
I just modified the DDL and the sql for adding the records.
I also added some code to delete the xls invoice if it already exists by the use of xp_cmdshell.
I think the problem is here. When a execute the procedure with the dbo account everything works fine, but with a regular user account nothing happens.
All involved sp have execute rights for the users.
So I have 2 quetions:
1) having DDL code in the sp needs special permissions?
2) is there an other way to delete a file without using XP_cmdshell(which needs special permissions)?
So basicly how do i run the code in the article with a regular user account?
Thanks
Serge
December 6, 2006 at 4:08 pm
1) having DDL code in the sp needs special permissions?
Yes...Depends on what the user is doing generally dbo permissions should be OK but becareful before granting...
2) is there an other way to delete a file without using XP_cmdshell(which needs special permissions)?
If you want to use sql there is no other way than xp_cmdshell which requires SA access OR you create PROXY account without SA access.
You delete the file using your VB/.net/CF code from the application instead of using sql.
MohammedU
Microsoft SQL Server MVP
December 7, 2006 at 12:32 am
Hi,
Would it work if I setup a job to trigger the stored procedure and then start the job from my outer sp as regular user?
Serge
December 11, 2006 at 4:29 pm
Agree with you on the dbo. Don't grant this unless absolutely necessary.
1) DDL
In order to create, alter, or drop the appropriate database object, the user will need the appropriate rights. The exception is if you're using temporary tables. Perhaps this is what you need to do?
BTW, one of the reasons things are probably failing is because the script uses the sp_OA* stored procedures. This basically allows you to instantiante and manipulate objects in SQL Server the same way GetObject/CreateObject does in VBScript/JScript. These stored procedures have no permissions set on them by default, which means only members of the sysadmin fixed server role can execute them. You can check on your own system by executing sp_helprotect '<object name>'
2) file deletes
You can use the sp_OA* stored procedures and the File Scripting object, however, this approach is much like xp_cmdshell. This is what you're script is doing in the first place, but to create an ADO connection.
K. Brian Kelley
@kbriankelley
December 11, 2006 at 5:21 pm
Yes, it possible...
You can scheduled the job to run every 1/5/30 minutes or so...
1. Create a table with file name and path ...
2. Write a proc to check the table if the row exists delete the file one at a time and delete the row...
if you want to keep deleted file information you add 0 OR 1 flag to the table and updated the flag once the file is deleted...
MohammedU
Microsoft SQL Server MVP
December 11, 2006 at 5:45 pm
In order to be able to start the job, the user must be the job owner or the member of a particular database role within msdb (whose use isn't supported). Otherwise the user cannot start the job.
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply