• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1955
  • Last Modified:

Permissions Problem

Hi,
I have a semi complicated Stored procedure running that uses a variety of Extended Stored procedures and extras.
For example;
Master..xp_fileexist
BULK INSERT
master..xp_cmdshell  

I understand that only a sysadmin user can run master..xp_cmdshell, so I have set up the proxy account and given sysadmin to that. cmdshell does seem to work now.
I think the problem is with the BULK INSERT. I have set the user windows group to be BULK INSERT ADMINISTRATORS but everytime the stored proc is run it fails.
I have set them up as sysadmin for a test and it works fine.
SQL 2000 SP3a is running
Access 2000 Front End

Any help would be appreciated. Maybe even a way to set User as sysadmin just while the stored proc runs? Possible?

Scott
0
scottsanpedro
Asked:
scottsanpedro
  • 4
  • 4
1 Solution
 
ispalenyCommented:
It can be filesystem permission issue.
0
 
scottsanpedroAuthor Commented:
No, its not, as by changing the security on SQL Server to sysadmin the procedure completes
Scott
0
 
ispalenyCommented:
For BULK INSERT, you need:

1. sysamin, dbo or object owner for table access
2. sysadmin or bulkadmin for BULK INSERT right
3. Data(format) file accesible for read by SQL Server service account.

I think doesn't have (1), he is not owner of the table.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
scottsanpedroAuthor Commented:
so to confirm.
The only way BULK INSERT wil work is if the user has sysadmin rights, or is the object owner.
Therefore its impossible for general users to actually run stored procedures that include this facility.
If that is definately so then I will have to completly re-write my code to be client side rather than server side.
I need a way for the user to be able to import the contents on a txt file to SQL Server on ad-hoc basis.
I would be happy for confirmation
Many thanks
Scott
0
 
scottsanpedroAuthor Commented:
OK. Found a way round this. I have setup different conenction strings, and use one with sysadmin rights just for this procedure
0
 
ispalenyCommented:
Everybody needs security with some ad-hoc exceptions:)

You can:
A) Use queue process  
1. Create a queue table with pairs UNCFilePath-Table and logging table
2. Create SP inserting into the queue table, check security there.
3. Create a process with very high right rights loading files from network. It can be SQL Agent and DTS combination.
    Schedule it to run every one minute. Store results in logging table.

B) Create https Web interface uploading files
 
0
 
ispalenyCommented:
>>OK. Found a way round this. I have setup different conenction strings, and use one with sysadmin rights just for this procedure
I don't like this too much. Now you have hardcoded sysadmin access in your client application. Every Windows localadmin is able to monitor connection, or even read it in code and get sysadmin access.
0
 
scottsanpedroAuthor Commented:
well we got there in the end. Exceptions it is :)
Many thanks for your time on this
Scott
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now