This is always an issue that all the permission are gone when ever databases are refreshed from a backup.The below script will script Server ,Database,Object and Database Role Level Permission for all Databases and all users.
Create a ServerList.txt
Download the Script_Server_Database_Level_Permission.sql and Powershell Script and save it to local drive location.
Replace the “Path” with correct path of the file ServerList.txt and Script_Server_Database_Level_Permission.sql in powershell script.
cls if (-not (Get-Module SQLPS)) {Import-Module SQLPS} #Get list of server from text files $SQLserverDB=@() $SQLserverDB=Get-Content "path\ServerList.txt Path" foreach($SqlServer in $SQLserverDB) { Write-Host "|- SQL Server : " $SqlServer -ForegroundColor Yellow try { $result=Invoke-Sqlcmd -ServerInstance $SqlServer -Database master -InputFile 'Path\Script_Server_Database_Level_Permission.sql' -QueryTimeout 0 -ErrorAction Stop $result |SELECT Script|ft -AutoSize } catch { $ErrorMessage = $_.Exception.Message $FailedItem = $_.Exception.ItemName Write-Host $_.Exception.Message "-" $_.Exception.ItemName } }
Ouput