SP authentication - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

SP authentication

Author

Topic

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-08-13 : 02:44:53

I have a store procedure, which will read a file from network and will insert the contents into a table. Now here I am facing some permission issues with this. While I access the file from a network shared computer it shows Operating system error code 5. So how can I set permission to this sql user to read the file.
Is there any way to pass the authentication parameters through procedure.

This is my procedure:

CREATE PROCEDURE addMobileNumbers
@PathFileName varchar(100),
@CustomerId numeric,
@FilePath varchar(250)

AS
DECLARE @SQL varchar(2000)
BEGIN
SET @SQL = "BULK INSERT tmpMobileNumbers FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = '\n') "
END
EXEC (@SQL)

INSERT MobileNumbers (MobileNumber,CustomerId,FilePath)
SELECT tmpMobileNumbers.MobileNumber,@CustomerId,
@FilePath
FROM tmpMobileNumbers

shaji

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 02:54:55

You must set READ access on the UNC path and file for the user running the query.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 02:59:04

If you are using Microsoft SQL Server 2005, you can use

EXECUTE AS

and name an user with permissions.

E 12°55'05.25"
N 56°04'39.16"

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-08-13 : 03:21:38

I already given the read permission to the file on the UNC. And what you mean by 'file for the user running the query'. In the code I am using 'sa' as the username. How to give the access permission to this 'sa' user?
I am using SQL server 2000

shaji

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-13 : 23:58:09

sa has full permission in sql server.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources