Script out Server – Database Level Permission for all users and from all databases

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

ServerList

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

PermissionOutput

Leave a Reply

Your email address will not be published. Required fields are marked *