Minimal permissions to get server directories from database server

Question

I find the SMO function for getting default directories very nice:

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server.backupdirectory.aspx

...but I cannot get it to work on a particular server. I get:

(Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: xp_regread() returned error 5, 'Access is denied.'
xp_regread() returned error 5, 'Access is denied.'
xp_regread() returned error 5, 'Access is denied.'
   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
...

Does someone know the minimal permissions for this function (and how to check this in advance?)

Cheers Dirkster

Answer 1

I believe you require elevated permissions as sysadmin on SQL Server to run xp_regread. i.e...

http://connect.microsoft.com/SQLServer/feedback/details/305538/xp-regread

...another option would be to write a CLR Stored Procedure which would give you more control over security.

Thanks

---

/Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005/2008), MCAD, ITILv3, OCA 11g) Please click the Mark as Answer button if a post solves your problem