Execute Permissions for master..xp_fileexist external store procedure

Question

Execute Permissions for master..xp_fileexist external store procedure

pkalva

SSC Eights!

Points: 962
More actions
May 26, 2009 at 7:34 am

#131534

Hi,
We have a sql server 2005 instance with sp2 and we are having problems executing external storeprocedure master..xp_fileexist. This store procedure can be executed using "sa" account but we want to execute this storeprocedure through non-sa account and without a "sysadmin" role.
Can any one please tell me on how can we achieve this?
Thanks!
Pallav.

Viewing 6 posts - 1 through 5 (of 5 total)

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

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

By default the public role has Execute permission on this procedure. I just checked it on 3 servers I recently installed.

So everybody should be able to use it.

If for some reason somebody removed the permissions you can use the GRANT statement to grant permissions to which ever user or role in the master database.

[font="Verdana"]Markus Bohse[/font]

pkalva SSC Eights! Points: 962 More actions · Answer 2

Hi Marcus,

Thanks! for the reply, sorry I should have been more clear. I see that it has execute permissions on public and I also gave exclusive execute permissions to user which has a problem.

When I execute master..xp_filexist for a specific file as "sa" it gives me output result as

FileExists FileisaDirectory ParentDirectoryExists

1 0 1

but when I execute as a user without a sysadmin role it gives me result as "0 0 0"

Thanks!

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 3

When you execute the procedure as sa, you use the windows credentials of the service account, which in a lot of cases is a local administrator on the server.

If a "normal" user execute the procedure the result will depend on the permissions, this user has on the OS. If your using a non-sysadmin SQL login to connect to SQL Server you won't have any permissions on the OS and the result will always be 0.

For Windows accounts it will depend on the permissions and group memberships of that account on the server.

[font="Verdana"]Markus Bohse[/font]

pkalva SSC Eights! Points: 962 More actions · Answer 4

Hi Marcus,

We are actually migrating this database from sql2000 to sql2005 it works fine in sql2000. A non-sa non-sysadmin sql account can execute this external store procedure and gets the same result as when an "sa" account runs.

Does sql2005 has any special restrictions?

Thanks!

Pallav

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 5

pkalva (5/26/2009)
Hi Marcus,
Does sql2005 has any special restrictions?
Thanks!
Pallav

Generally speaking, security is a lot tighter in SQL 2005. Also xp_fileexist is an undocumented procedure and MS won't tell you exactly what has changed. I remember that the Upgrade Advisor had some warnings about extended stored procedures which are no longer supported or have changed, but I don't have the details available right now.

[font="Verdana"]Markus Bohse[/font]