3

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.

chandler3224
  • 121
  • 1
  • 8
  • Afaik such permissions can be granted through a certificate-signed stored procedure. It might look a little complicated, but it's the best way to implement permission elevations in MSSQL. Never tried it myself, though. – Roger Wolf Aug 19 '14 at 05:44

2 Answers2

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.

Granger
  • 3,639
  • 4
  • 36
  • 34
  • This didn't work for me. I had to use `EXECUTE AS OWNER` and make sure the database was owned by a login with `sysadmin` privileges (`sa`, for instance). `ALTER DATABASE [DBNAME] SET TRUSTWORTHY ON;` is also required. – Guillermo Prandi Jan 26 '21 at 18:09
  • It sounds like you didn't execute that as "sa", though the "trustworthy" part makes sense. – Granger Jan 26 '21 at 20:41
  • I didn't. I used another login that was `sysadmin` too. – Guillermo Prandi Jan 26 '21 at 20:44
  • I always use "SELF" because it's easier for me to test. I can run the code before I create the module and verify it's going to work. When using "OWNER", those permissions are from the schema owner ("dbo" in my example), by default. – Granger Aug 02 '21 at 14:06
  • Given your symptoms, something else was going on, or the [MS docs](https://learn.microsoft.com/en-us/sql/t-sql/statements/execute-as-clause-transact-sql?view=sql-server-ver15) are missing some critical wrinkle. Based on your comments, both approaches ultimately used a "sysadmin" account. – Granger Aug 02 '21 at 14:12
-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.

Wes Palmer
  • 880
  • 4
  • 15
  • This answer does not work. The error reported is that the user is not a sysadmin. [Related link](http://www.databasejournal.com/features/mssql/xpcmdshell-for-non-system-admin-individuals.html) and [SO link](http://stackoverflow.com/questions/2016669/getting-execute-permission-to-xp-cmdshell). – crokusek Dec 02 '15 at 01:03
  • So don't down vote the answer because the answer works 100% and you just don't have the correct privledges. – Wes Palmer Dec 02 '15 at 14:30
  • SInce you don't have SA rights you will have to talk to the DBA's and have them grant you access to run the stored procedure. It sounds like your rights right now are already limited on the box too. Do you know what rights you have? – Wes Palmer Dec 02 '15 at 14:33
  • Let me re-phrase, the answer does not work even after the recommended permission has been successfully granted. The error message says that the user is not a sysadmin. Tested on 2012. – crokusek Dec 02 '15 at 18:31
  • I just tested it and it works. The user you want to grant the access for need to at least have public access to the DB. – Wes Palmer Dec 02 '15 at 20:41
  • I just tested it and it works. The user you want to grant the access for need to at least have public access to the master database because you are granting access to a stored procedure in master. GRANT EXECUTE ON OBJECT::master.dbo.xp_create_subdir TO testUser; In this example testUser is a sql user and has only public access to master. And it works perfectly. – Wes Palmer Dec 02 '15 at 20:47
  • Perhaps your system had already setup a [proxy account](http://www.databasejournal.com/features/mssql/xpcmdshell-for-non-system-admin-individuals.html)? That article also mentions to grant execute on the SP once the proxy account is working and the error message changes. Can you verify that there is no proxy account? Would that be listed in [master].sys.credentials or msdb..sysproxies? – crokusek Dec 02 '15 at 21:56
  • I just used a SQL Creates SQL User. Not a windows user or AD user. We have proxy accounts but they weren't used in this solution. – Wes Palmer Dec 03 '15 at 14:53
  • I'm talking about [Creating Proxy Credentials for xp_cmdshell](http://www.databasejournal.com/features/mssql/xpcmdshell-for-non-system-admin-individuals.html). Seems your install may have done this. On that page there is also way to disable it which you could run and re-test to prove that it was not the reason why it works. – crokusek Dec 12 '15 at 01:00