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.
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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?
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-04-17 : 14:18:44
May try setup proxy account.
Go to Top of Page

lobsterZoom
Starting Member

9 Posts

Posted - 2007-04-23 : 04:22:04
(Spam Removed)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

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


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
   

- Advertisement -