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?
Best Answer
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: