September 23, 2011 at 3:17 pm
Hi,
We are using SQL 2008 R2 SP1.
There is an existing maintenance plan which successfully backs up several databases to a network share. As part of a security review, I am trying to change the maintenance plan to run under a proxy account with minimal permissions rather than the SQL Agent account.
The job fails.
The steps I have taken so far are:
~ Created a security credential for a domain account with read/write permissions on the network share
~ Created a proxy account linked to the credential
~ Added the proxy a/c to the Operating System AND SSIS subsystems.
~ Created a SQL server login for the domain account
~ Added the login to the db_backupoperator database role in all databases to be backed up
~ Added the login to the db_ssisltduser and db_ssisoperator database roles in MSDB
~ Granted the login permissions in MSDB on several maintenance plan related system tables/stored procedures
~ Updated the SQL Agent job to "Run As" the new proxy account
Having completed all of these steps the job is now failing with the following error message:
Error executing 'xp_create_subdir': Permission denied. User must be a member of 'sysadmin' server role
There is very little documentation around xp_create_subdir, but I believe this is being called because the "create a sub-directory for each database" option has been selected in the maintenance plan?
Is there any way around this? Does the user really have to be a sysadmin? That's exactly what I'm trying to avoid and why I'm going through all the steps to try and tighten up the permissions on the maintenance plan.
I guess the easy option would be to untick that option and have all the database backups in the same folder, thus avoiding the extended procedure call, but I'd really appreciate your feedback on this.
Thanks,
Matt.
September 25, 2011 at 12:44 pm
have you explicitly granted the domain account execute on xp_create_subdir in the master database?
---------------------------------------------------------------------
September 26, 2011 at 5:16 am
Thanks for your reply.
I've tried granting the domain account EXECUTE permission on xp_create_subdir, but it still returns the same error message.
I found the following on MSDN regarding permissions on extended procedures:
Granting permission to execute or select an object does not necessarily convey all the permissions required to use the object. Most objects perform operations for which additional permissions are required. For example, a user that is granted EXECUTE permission on sp_addlinkedserver cannot create a linked server unless the user is also a member of the sysadmin fixed server role.
I've unticked the option to create a subdirectory for each database, and the backups are now running ok under the proxy, although obviously all the backups are now in one folder which isn't what I originally wanted.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply