Run xp_create_subdir without admin privileges
Point: I want to be able to create a directory on the filesystem via a non-sysadmin user.
I am creating a web-based deployment script that creates new databases from a specified template database.
Basically, I create a backup of the specified template database and then restore it as a new database with a different name.
Our database server has our client databases stored in subfolders in our database store. If I were using the default settings, it would look something like this:
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 in a separate window), so I cannot execute anything other than SQL.
My database user is extremely limited, however I would like to somehow give this user access / execute only master.dbo.xp_create_subdir. Is this even possible?
I don't want to give our local administrators system administrator rights, it has a limited user for some reason.
Database Server - Microsoft SQL Server 2008 R2.
Cheers, any help would be appreciated.
You can give the user access to use this stored proc explicitly. It will be something like:
GRANT EXECUTE ON OBJECT::master.dbo.xp_create_subdir
TO <SQL USER>;
It looks like this user is being restricted for some reason, although getting extra permissions to run something like this might take a little off the control of the DB manager. Therefore, be careful when dealing with getting elevated privileges.