xp_fixeddrives with limited rights no results!? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

SQL Server Administration (2005)

xp_fixeddrives with limited rights no results!?

Author

Topic

Wang
Starting Member

48 Posts

Posted - 2007-04-17 : 06:31:47

Hi

Does anyone here know why xp_fixeddrives with a limited user returns an empty result set on 2005?

In essence I want to create a report which shows disk space remaining, an upgrade of an old one. It works fine on 2000, but seems to need sysadmin rights on 2k5. I would appreciate it if someone can suggest how to get it working - or an alternative solution!

Cheers

bpgupta
Yak Posting Veteran

75 Posts

Posted - 2007-04-17 : 07:17:42

In 2k5 we need to have permisstion to executed the Extended proc , we can see the permisstion there or not by exectuing the

select user_name(grantee_principal_id), permission_name, state_desc from sys.database_permissions where major_id = object_id('xp_fixeddirves')

if not u have to grant the permisstion for that user.

Wang
Starting Member

48 Posts

Posted - 2007-04-17 : 08:35:44

Hmm.

Ok, I have public with exec, granted on xp_fixeddrives.


create login bob 
	WITH PASSWORD = 'b0b', 
	CHECK_POLICY = OFF 

master..sp_adduser bob
grant exec on xp_fixeddrives to bob

Bob now shows up in your code with exec granted on xp_fixeddrives.
Still no records. The proc execs fine (as before) but just returns an empty set.

bpgupta
Yak Posting Veteran

75 Posts

Posted - 2007-04-17 : 08:39:36

it is working fine in 2005 also..
can u post how u r executing it

Wang
Starting Member

48 Posts

Posted - 2007-04-17 : 08:59:14

well, as above.

Then login as bob and run

 exec master..xp_fixeddrives

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-04-17 : 11:17:07

User needs permission in OS also to get info, that's why works for sysadmin.

Wang
Starting Member

48 Posts

Posted - 2007-04-17 : 11:21:30

So how do I give a limited sql login rights on the server to get this data?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-04-17 : 14:18:44

May try setup proxy account.

lobsterZoom
Starting Member

9 Posts

Posted - 2007-04-23 : 04:22:04

(Spam Removed)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-23 : 04:28:16

SPAM!

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources