SQL Server Login Access getting revoked by a daily database refresh



  • Scenario : I have granted dbdatareader access on a database to the dev team. They do have daily database refresh in place . Because of this the permissions to the login , which is a SQL login are getting revoked daily because of this database refresh job.

    Since Dev team cannot add a block to add permissions automatically after the database refresh , how should I as sysadmin deal this case . I get tickets daily to grant read-only access for that database. I cannot grant them dbaccessadmin due to security issues.

    Please advice.



  • You could try creating a SQL Agent job that runs once an hour and checks if the permission is present. If not, add the permission back. With it being a dev region, I wouldn't be super worried about this approach.

    Something like...

    IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [name] = '')
        CREATE USER [] FROM LOGIN 
    

    ALTER ROLE db_datareader ADD MEMBER <dev_user>




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2