Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I need to be able to remotely monitor the disk space on a SQL 2005 server. To do this I need to give a sql server user the ability to run the following stored procedure:

EXEC xp_fixeddrives;

Ideally this user wouldn't have permission to run other stored procedures or do much of anything else.

The new user I just created currently doesn't have permission to run the stored procedure at all. What is the best way to give the user permission to do this and nothing else?

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

xp_fixeddrives is an undocumented procedure. So basically, it doesn't exists. the is no documented way to grant, deny or revoke any permission on it and the result of executing it can be basically anything.

That being said, you can try to wrap the XP in an ordinary procedure, then use code signing to grant the needed permissions to the procedure, then grant EXEC to your user on the wrapper, similar to the example in my blog: Signing an activated procedure. This works on my machine. Your mileage may vary, as is the case anytime you use undocumented functionality:

use master;
go

create procedure usp_fixeddrives
with execute as caller
as
begin
  exec xp_fixeddrives;
end
go

grant execute on usp_fixeddrives to [low_priviledged_user];
go

create certificate [usp_fixeddrives]
  encryption by password = 'AnyPassword@1234!'
  with subject = N'usp_fixeddrives'
  , start_date = '11/05/2009';
go

add signature to [usp_fixeddrives]
  by certificate [usp_fixeddrives]
    with password ='AnyPassword@1234!';
go

create login [usp_fixeddrives] from certificate [usp_fixeddrives];
go

grant authenticate server to [usp_fixeddrives];
grant control server to [usp_fixeddrives];
go

alter certificate [usp_fixeddrives] remove private key;
go
share|improve this answer
add comment

Should be able to to do:

grant execute on xp_fixeddrives to username

go

share|improve this answer
    
I get: "Msg 15151, Level 16, State 1, Line 1 Cannot find the user 'dforgac', because it does not exist or you do not have permission." I am logged in as a server administrator using Windows Authenticaiton so I should have permission. The username exists. –  Dave Forgac Nov 6 '09 at 19:35
    
You'll first need to add the login as a user to the master database. –  squillman Nov 6 '09 at 19:42
    
Thanks! I added the public role on the master database to the user and then was able to set the permissions as above. –  Dave Forgac Nov 6 '09 at 20:13
    
Well the permissions were applied without error but now when I execute xp_fixeddrives as the new user I get 0 results. –  Dave Forgac Nov 6 '09 at 20:26
add comment

You'll need to use EXECUTE AS for this in SQL 2005, and (I think) impersonate a sysadmin who's also an admin for the physical server. A regular user has no rights to the file system, hence no results. In SQL 2000, simply granting them rights to the SP would have worked.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.