Post reply

Using db_backupoperator role and adding new databases

  • February 17, 2011 at 4:17 pm

    #245676

    OK, I have a SQL agent backup job created through the maintenance plan which performs a backup of ALL dabases on the instance. The job owner is set to 'sa'. Now we want to set the job owner to a specified domain account known as 'sqlbackupadmin'. Obviously we don't want to give this account any more rights than it needs so we don't want to give it sysadmin rights.

    So we've thought of mapping this domain account to all databases with a database role membership of db_backupoperator (this is done through login account > properties > User Mapping).

    And there lies my question. Is there a way to automate this so that whenever a NEW database gets added, the sqlbackupadmin account will automatically be mapped to the new database with the db_backupoperator role enabled?

    Of course, please feel free to offer tips on a better way to tackle this. The bosses are leaning towards this method because they want job application functions run under specific domain accounts. Our SQL servers are running under a service account too.

    Thanks in advance to all who reply. Much appreciated.

  • February 18, 2011 at 1:18 am

    #1288670

    Adding this user to the model database, associated with db_backupoperator role will do.

    Every new database will be created with this user mapping already set.

    Hope this helps

    Gianluca

    -- Gianluca Sartori

  • February 18, 2011 at 1:20 am

    #1288671

    dso808 (2/17/2011)

    The bosses are leaning towards this method because they want job application functions run under specific domain accounts. Our SQL servers are running under a service account too.

    I forgot to mention that I agree with this method and we are doing the same here.

    Gianluca

    -- Gianluca Sartori

  • February 22, 2011 at 12:05 pm

    #1290031

    Thanks Gianluca! Sounds great. Appreciate the help and have a great week.

  • February 23, 2011 at 10:56 am

    #1290544

    Hi again Gianluca. Sorry, I spoke too soon. My backup job failed with an error stating that 'Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account'.

    Background: my backup job was created with the management plan wizard. The job owner is set to 'sqlbackupadmin' which is the domain account that I gave the role of 'db_backupoperator' to all the databases in the instance and also added to the model database as you advised.

    We still don't want to give sysadmin rights to 'sqlbackupadmin'. Is there any other way to do this, without creating a proxy account? Thanks again!

  • February 24, 2011 at 12:58 am

    #1290760

    I don't see any way other than creating a proxy account. Why is it a problem for you?

    -- Gianluca Sartori

  • February 24, 2011 at 10:48 am

    #1291129

    Hi Gianluca and thanks again for the reply. My apologies - I was worried about creating a proxy account because I thought that I'd have to jump through hoops to get it done (I work at a financial institution). But that turned out to not be the case. OK, I'll give it a shot. Thanks again!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply