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.
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.
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [name] = '') CREATE USER  FROM LOGIN
ALTER ROLE db_datareader ADD MEMBER <dev_user>