Run xp_create_subdir without admin privilidges

Question

The Point: I want to be able to create a directory on the filesystem through a non-sysadmin SQL user.

I'm creating a web front-end for a deployment script which creates new databases from a specified template database.

Essentially I'm backing up said template database and then restoring this as a brand new database with a different name.

Our DB server has our client databases stored in sub-folders within our database store. If I were to use the default settings it would look something like:

D:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\[ClientRef]\[ClientRef].mdf D:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\[ClientRef]\[ClientRef].ldf

I only have SQL access to the database server (via a programming language, hosted on a separate box) so I can't execute anything other than SQL.

My database user is extremely limited, however I would like to somehow grant this user to access/execute master.dbo.xp_create_subdir only. Is this possible at all?

I'm loathe to give our local DB user sys-admin rights, it has a limited user for a reason.

DB Server is Microsoft SQL Server 2008 R2.

Cheers, any help will be appreciated.

Answer 1

One possible solution is to write your own sproc that internally uses master.dbo.xp_create_subdir.

Create the sproc while logged in as an account that's a member of the sysadmin role and use "WITH EXECUTE AS SELF". Then grant permissions to that other account to execute this sproc. The database catalog where you create this wrapper-sproc must be marked as "trustworthy" or you'll still get the: User must be a member of 'sysadmin' server role. error.

E.g.

CREATE PROCEDURE [dbo].[sprocAssureDirectory] @directoryFullPath varchar(4000)
WITH EXECUTE AS SELF 
AS
BEGIN
    EXEC master.dbo.xp_create_subdir @directoryFullPath;
END

Just make sure you add any needed assertions/checks to your sproc that make sense for your application (e.g. the path can only be of a pattern that you expect).

Belated Update: Added the critical mention of marking the catalog as trustworthy.

Answer 2

You could give access for the user to use that stored proc explicitly. It is gonna be something like:

GRANT EXECUTE ON OBJECT::master.dbo.xp_create_subdir 
TO <SQL USER>;

It sounds like that user is limited for a reason though and getting the extra permissions to run something like that can get a little push back from whoever is managing the DB. So be careful when dealing with getting the elevated privledges.