Post reply

SQL account for restoring database

  • June 2, 2020 at 2:31 pm

    Go to Answer

    #3759388

    I have a script that restores a database and runs some post-restoring actions, mostly fixing the database users. The scripts runs under SQL account ScriptRunner. The RESTORE is fine since ScriptRunner is assigned the dbcreator server role.

    But how can I run the post-restore script if all database users, including the one for ScriptRunner, are gone after RESTORE? Who will fix the ScriptRunner database user? One solution is to run the post-restore script under a sysadmin account. Is there anything better?

  • June 2, 2020 at 2:48 pm

    #3759394

    Yeah, that's rather tricky.  All I can think of now is to create a stored proc with an " EXEC AS 'powerful_user_name' " clause, then give the ScriptRunner the authority to run that proc.  That proc could then do the db user adjustments.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • June 2, 2020 at 5:49 pm

    Answer

    #3759539

    These probably shouldn't be "scripts".  They should be stored procedures (possibly in a permanent "utility" database) and then you can do what Scott has suggested to keep things safe.

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 2, 2020 at 7:45 pm

    #3759569

    Thanks, Scott and Jeff, I like your ideas.

Viewing 4 posts - 1 through 3 (of 3 total)

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