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.
source to share
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.
source to share