Regarding extended stored procedure.

Question

Post reply

Regarding extended stored procedure.

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

saurabh.punn SSC Enthusiast Points: 181 More actions · Answer 1

Hi

This is wht i m got:

Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1

The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

saurabh.punn SSC Enthusiast Points: 181 More actions · Answer 2

And 1 more thg:

Create proc A

with execute as 'dbo' or

with execute as owner ,wht shd be used instead of this dbo and owner?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

saurabh.punn (7/22/2008)
Hi
This is wht i m got:
Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

Please ask your dba to run it as a test.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

saurabh.punn SSC Enthusiast Points: 181 More actions · Answer 4

Gail

Dba created a test user and ten gave it the following permissions:

With execute as 'sa'

Grant impersonate on user::'test' on 'sa'

Even this thg also didnt work out as he said.

N i cant verify it as i dont have required access.

Is it like that in 2005 XP_Fileexist can only work if run under sysadmin role?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Please ask your DBA (who I assume has sysadmin permissions) to run the xp_cmdshell as requested earlier and see what results you get back.

With execute as 'sa'
Grant impersonate on user::'test' on 'sa'
Even this thg also didnt work out as he said.

Did it throw an error message?

Did it work but produce the wrong answer.

Please be specific. I can't see your server and I can't read your mind.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

saurabh.punn SSC Enthusiast Points: 181 More actions · Answer 6

Hi Gail

for my DBA every thg is working fine

he is the sysadmin

but the issue is ven a person who is not in sysadmin role is not able to execute the XP_Fileexist extended stored procedure.

He said he tried every thg including the xp_cmdshell n for him every thg is working fine.

finally he said tht since xp_fileexist is an undocumented sp microsoft have changed its security.

Only people with sysadmin access can execute it

Even impersonation as u suggested didnt work out he said.

I m presently trying on using command prompt to solve out tis issue

IF EXIST is a command prompt command which checks for a file location as xp_fileexist do.

It worked wen i supplied the same path and file location.

But the issue is im supplying values from command line n wht i want is tht instead of providing parameters from command line i shd provide it from a table in sql 2005 where they have stored the path name and file name.

I have posted a topic under using OSQL to deal with tis...

Can u plz chk it n help me out.

Thnks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

What did the xp_cmdshell return for him? I know it will work, I'm interested in what it shows when you try to do a DIR of the serverA.

What error are you getting back from the xp_fileexists when you run it with the impersonation?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

saurabh.punn SSC Enthusiast Points: 181 More actions · Answer 8

Gail

The DBA said Vendor of the application will now deal vid tis.

Thanks really for ur cooperation and help.