Removing production permissions when restoring database to non-prod

• webrunner

  SSC-Dedicated

  Points: 32231

  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 and 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

  -------------------
  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