July 11, 2008 at 7:56 pm
Hi all
I am working on an application which is 2 b migrated from sql 2000 to sql 2005.
We have a copy of the production database(suppose in ServerA) in the new development database(suppose in Server B).
There is a procedure that takes 2 parameters:
@filepath-that indicates where is file on which we have to work on
@filename-name of the file.
This procedure passes these values to an extended stored procedure xp_fileexist.
Now the problem is when i m giving the path like \\Server B\file.txt the proc xp_fileexist is giving the proper output.But if im giving the path like \\Server A\file.txt ,the proc xp_fileexist is not recognizing the path at all even if the same file is there and the path is also 100% correct.I m executing the stored procedure from a remote machine having SQL 2005 installed in it.
Is there is some prob in the extended stored procedure in SQL 2005?
Please help me in resolving this issue..
Thanks in advance.
July 12, 2008 at 5:59 am
Does the SQL service account have rights to connect to the share on Server A?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 14, 2008 at 5:18 am
Hi Gail..
What exactly u mean by rights??
on Server A im havin local admin access.
Can u plz illustrate on this Rights thing.
Thanks
July 14, 2008 at 5:22 am
The permissions that you have to the server is irrelevant. It's the account that SQL is running as that is important here.
What account is SQL Server running as?
Does that account (not yours) have permission to access the share on Server A?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 14, 2008 at 6:06 am
Read the following post for more information.
http://www.sqlservercentral.com/articles/Stored+Procedures/xpfileexist/183/">
http://www.sqlservercentral.com/articles/Stored+Procedures/xpfileexist/183/
Also, You might be running SQL Service with LocalSystem or a local machine account.
Atif Sheikh
July 15, 2008 at 8:16 am
Hi Gail/Atif
I have added the service id under which the sql services are running in SQL server 2005 development server to the server A.
still no result.
July 15, 2008 at 12:34 pm
What do you get returned by the following (fix directory and server names if necessary)
exec master..xp_cmdshell 'dir \\Server A\*.*'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 17, 2008 at 2:34 am
Hi Gail
i had a chat vid my DBA so ven he gave me admin rights as he said then i was able to execute it.
But as he cant gives priveleged access to every one so v hav 2 look for a replacement.
Is there any replacement for this XP_Fileexist which doesnt require such priveleged access like admin?
July 17, 2008 at 4:05 am
xp_cmdshell with the appropriate proxy account created.
Look up proxy aaccounts in books online. You don't have to be admin to execute xp_cmdshell
If you're using stored procedures, consider the EXECUTE AS clause on the procedure to let the proc run as a sysadmin even if the user calling it is not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2008 at 1:54 am
Hi Gail
i tried executing the procedure by using the execute as clause.
its still not working.
Create procedure Test
with execute as owner
Can u help me out vid tis?
Thanks in advance.
July 22, 2008 at 6:08 am
Please give more info about what you're trying and what permission level various users have.
Is the owner of that proc a sysadmn? If not, that particular execute as is useless. Look at the other options that there are for execute as.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2008 at 8:05 am
Hi Gail
i asked my dba 2 create a sp with same information as the one which i m tryin 2 run.
So he created it by including the execute as clause.
But ven i tried executing it again output came 0 but it shd b 1 as file path is 100% rite n the file which the path is referring 2 is also there.
He said xp_fileexist requires sysadmin access in sql 2005 to execute which he cant give.
With execute as clause i found u can use the following:
owner,self,caller,user_name.
Im nt sure vich 1 will work.
This is whole situation.
Thanks
July 22, 2008 at 9:13 am
Please ask your dba if it is acceptable for the stored proc to impersonate a sysadmin login.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2008 at 10:50 am
Hi Gail
he did the following:
Create procedure Test
with execute as 'dbo'
Then also it is not working.
one thg is not clear:
i am logging in sql 2005 management studio.
ten connecting to the db server A.
whenever i m trying 2 execute the xp_fileexist proc by mentioning a path which is in the server a the sp is running fine.
but venever i m giving any other path it is not.
July 22, 2008 at 10:55 am
No it won't. dbo is not a server-level principal. It's a database-level principal.
Did you ask him about impersonating a sysadmin?
whenever i m trying 2 execute the xp_fileexist proc by mentioning a path which is in the server a the sp is running fine.
but venever i m giving any other path it is not.
Does the SQL server service account have rights to access the other servers?
If you run xp_cmdshell and try and do a dir of the directories on the remote servers (other than server A) what do you get back?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply