Removing production permissions when restoring database to non-prod

• webrunner

  SSC-Dedicated

  Points: 32233

  More actions

  August 3, 2026 at 5:51 pm

  #4918193

  Hello experts,

  An application team has asked us to modify a SQL Agent job. The job currently restores a backup of the production database to their test SQL Server. We do have provisions for masking the non-prod data. However, for years I have used a script to preserve the test db permissions and then replace them after. It's very crude - I script out the permissions and add them to a job step.

  Given that current setup, I have two questions:

  1. What would be the best way to dynamically preserve and replace the permissions on the fly, rather than using hard-coded script output?
  2. What would be the best way to completely remove any production permissions without messing up any existing test permissions that happen to have the same name (user, role, etc.)?

  I've tried to prompt AI about this, but it usually ends up being a long list of scripts that don't always work, and I also suspect there is a cleaner way.

  Thanks for any help,

  webrunnre

  • This topic was modified 59 minutes ago by webrunner.

  -------------------
  A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
  Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

• frederico_fonseca

  SSCoach

  Points: 16338

  More actions

  August 3, 2026 at 6:32 pm

  #4918230

  one way to do it is to have 3 steps.

  1.  (powershell  (saves output to file) or tsql (saves output to DBA database)) scripts all required users and permissions.
  2. restore from prod
  3. remove existing users/permissions and apply saved ones.