restore database and create users using minimal elevated privilege

• martin 22999

  SSC Enthusiast

  Points: 126

  More actions

  April 29, 2015 at 9:43 pm

  #317189

  Hi I'm having problems 🙁 that I hope you can help me resolve.

  What I want to do is :

  - restore a backup of a 3rd party database onto one of our servers

  - this has no users that I can use

  - there is some ETL processing so we're using Control-M to manage the process

  - create a database user and grant it db_reader.

  I'd like to do this without granting any users elevated privileges if possible.

  What I've done so far is grant the Control-M user (this is a domain user) dbcreator rights and made it owner of our copy of the database that is being refreshed.

  The refresh is completing, but Control-M is not able to log onto the database to create the user.

  Can you tell me the best way to accomplish this task without granting the control-m user sysadmin rights?

  Would I be able to do it if I used a SQL Agent job for the restore and user crreation?

  thanks

  Martin

• bnordberg

  SSCertifiable

  Points: 5379

  More actions

  April 30, 2015 at 9:22 am

  #1794737

  Using SQL Agent to do this would be better as it typically already has elevated permissions.

  Let it do the restore, then have it just grant the needed permissions (read, write, create table...) to the Control-M user.

  As well, db_creator role can DELETE databases they don't own, so I'm not sure you haven't already given that user elevated permissions.