Solved

Problem with the folder given

Posted on 2013-01-10
11
373 Views
Last Modified: 2013-01-12
Hi,
This statement is working fine and also the image file goes to the table in its original format.
update dbo.user_master
set photo_file=(SELECT BulkColumn
FROM OPENROWSET(BULK N'C:\t93.jpg', SINGLE_BLOB) AS SRC)
where user_abbr='pc1';

But why do I get the problem for this, while "C:\setup\t93.jpg" is surely existing in the server with fine file format?
update dbo.user_master
set photo_file=(SELECT BulkColumn
FROM OPENROWSET(BULK N'C:\setup\t93.jpg', SINGLE_BLOB) AS SRC)
where user_abbr='pc1';

Msg 4861, Level 16, State 1, Line 2
Cannot bulk load because the file "C:\setup\t93.jpg" could not be opened. Operating system error code 5(Access is denied.).

How to ensure one DB user is able to upload whatever file within whatever folder of the server?
0
Comment
Question by:HuaMinChen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
11 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 334 total points
ID: 38765939
You need to make sure that the user that is running the SQL Server service (not the user you connect to the database engine with) has access to the directory you're trying to read the file from.
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 38765992
How to make sure the DB user is able to access the folders of the machine? Thanks
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 334 total points
ID: 38766006
I'll have to defer to someone who knows Windows level security better than me, I'm afraid.

Check out the Security section of these two documents:

http://msdn.microsoft.com/en-us/library/ms188365.aspx
http://msdn.microsoft.com/en-us/library/ms175915.aspx

I believe that the section on "Security Account Delegation (Impersonation)" is particularly relevant.
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 10

Author Comment

by:HuaMinChen
ID: 38766031
No, there can be some control in Sql server on the DB user's privileges to access the folders of the current Windows server, can't there?
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38766049
Nope - it's all about the SQL Server service owner account and the privileges it has.  Read those links from my earlier response.
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 38766062
If you see the statements I've run above, one is with "c:\setup" which leads to the problem, one with "c:\" which is fine. This means the current DB user is having access to "c:\" while not to "c:\setup", as I am using the same DB user for doing the above. Then where is the control to make "c:\setup" "available/seeable" to the current DB user.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 166 total points
ID: 38769234
This means the current DB user is having access to "c:\" while not to "c:\setup", as I am using the same DB user for doing the above.
No, it does not mean that.  As indicated previously, this has nothing to do with the database user and everything to do with the SQL Server service startup account.
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 38769408
I've requested that this question be deleted for the following reason:

I have not got one clear answer with satisfaction to this and this is no more needed. Thanks
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38769409
The answers given were correct - the security on the SQL Server service owner account needs to be modified to have access to whatever directories are needed for the bulk insert.  Links to Microsoft KB articles were provided.  

The remaining unanswered portion is the "how" on the Windows Security side, but that is a Windows OS issue, not a Database issue so I believe the question was satisfactorily answered.
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 38770012
I've requested that this question be deleted for the following reason:

I said this is no more needed as there is no "fine" resolution given.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
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 to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

739 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