rian
asked on
Stored Procedure checking for File existance
Can a stored procedure read a physical drive. I want to make an stored procedure that sets a Boolean value based on the existence of a file on a drive. It would not be located on the same server as SQL. Are there any security issues too. An example would be nice.
I usually do it via a dir command but you could also use the file system object.
create procedure spCheck
@filename varchar(128) ,
@fileexists int output
as
select @fileexists = 0
create table #a (s varchar(1000))
declare @sql varchar(1000)
select @sql = 'dir c:\dir\*.*'
insert #a exec xp_cmdshell @sql
if exists (select * from #a where s like '%' + @filename + '%'
select @fileexists = 1
go
You will need permission on the directory for the user context under which xp_cmdshell runs on your system - probably the sql server service.
create procedure spCheck
@filename varchar(128) ,
@fileexists int output
as
select @fileexists = 0
create table #a (s varchar(1000))
declare @sql varchar(1000)
select @sql = 'dir c:\dir\*.*'
insert #a exec xp_cmdshell @sql
if exists (select * from #a where s like '%' + @filename + '%'
select @fileexists = 1
go
You will need permission on the directory for the user context under which xp_cmdshell runs on your system - probably the sql server service.
an extended stored procedure xp_fileexist migth be of help.
execute master.dbo.xp_fileexist 'C:\boot.ini'
output
[File Exists] 1 -- integer
[File is a Directory] 0 --integer
[Parent Directory Exists] 1 --integer
execute master.dbo.xp_fileexist 'C:\boot.ini'
output
[File Exists] 1 -- integer
[File is a Directory] 0 --integer
[Parent Directory Exists] 1 --integer
>>it would not be located on the same server as SQL
This said, the stored proc can only see local drives to the SQL Server, for drives on other PC it would be an UNC path (\\workstation\C$\), for which the (admin) permissions need to be given
CHeers
This said, the stored proc can only see local drives to the SQL Server, for drives on other PC it would be an UNC path (\\workstation\C$\), for which the (admin) permissions need to be given
CHeers
ASKER
WHat kind of security do I need to setup. I have a server called MAserver which is connected to my machine. However when I do a
execute master.dbo.xp_fileexist '\\maserver\cdrive\config. sys'
it gives me 0,0,0 in all three fields. that file does exist. and I am logged on using query analyzer as sa. plus xp_fileexist is set for public access.
Ofcourse, this procedure will probably be run on individual sql accounts.
execute master.dbo.xp_fileexist '\\maserver\cdrive\config.
it gives me 0,0,0 in all three fields. that file does exist. and I am logged on using query analyzer as sa. plus xp_fileexist is set for public access.
Ofcourse, this procedure will probably be run on individual sql accounts.
Looks like stored procedure is working, it returned zeros, indicative that it was un-able to see the share.
I would verify account running sql server, it need to be domain account, not a local to the machine running sql server, also this account need to have "full control" privilege granted to access the share, where file was checked.
Cheers
I would verify account running sql server, it need to be domain account, not a local to the machine running sql server, also this account need to have "full control" privilege granted to access the share, where file was checked.
Cheers
ASKER
I am not sure how you can set security setting in Windows for SQL users only. For example, I have a SQL user called joe with password joeb and when the user logs on, the sql connection is made with his username and password.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
rian:
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.
2000 Mapping Error Date: 07/25/2001 01:53PM PST
https://www.experts-exchange.com/questions/20157467/2000-Mapping-Error.html
Flex Grid Question Date: 06/06/2002 01:09PM PST
https://www.experts-exchange.com/questions/20308730/Flex-Grid-Question.html
FlexGrid Multiple selection Date: 04/16/2002 08:21AM PST
https://www.experts-exchange.com/questions/20290075/FlexGrid-Multiple-selection.html
Thanks,
Anthony