Get assigned DB roles from Azure DB

SELECT DP1.name AS DatabaseRoleName,
isnull (DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
ORDER BY DP1.name;

Clean up script for subfolder in users

[int]$retentionDays = 90
$folders = "downloads", "OneDrive\Microsoft Teams-chatfiles"

$filesForDeletion = @()
$users = (Get-ChildItem "c:\users").name

foreach ($user in $users) {
foreach ($folder in $folders) {

$path = "C:\users\"+$user+"\"+$folder
if (Test-Path $path) {
$files = Get-ChildItem $path

foreach ($file in $files) {

if ($file.CreationTime -lt (Get-Date).AddDays(-$retentionDays)) {
$filesForDeletion += $file
}}}}}

rm $filesForDeletion.fullname -Confirm:$false -Force -Recurse

Connect Powershell to Exchange Online

Check for connection, if not, then Establish connection

# Check for connection
$NotConnected = $true
$RMsessions = Get-PSSession
Foreach ($RMsession in $RMsessions) {
if ($RMsession.Computername -like "*outlook*") { $NotConnected = $false }
}

# Establish connection
if ($NotConnected ) {
$LiveCred = Get-Credential
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell/ -Credential $LiveCred -Authentication Basic -AllowRedirection
Import-PSSession $Session 
cls
}

Trigger Azure Policy to evaluate compliance

$subscriptionId = "!!!SUBSCRIPTION ID!!!"

$uri = "https://management.azure.com/subscriptions/$subscriptionId/providers/Microsoft.PolicyInsights/policyStates/latest/triggerEvaluation?api-version=2018-07-01-preview"
$azContext = Get-AzContext
$azProfile = [Microsoft.Azure.Commands.Common.Authentication.Abstractions.AzureRmProfileProvider]::Instance.Profile
$profileClient = New-Object -TypeName Microsoft.Azure.Commands.ResourceManager.Common.RMProfileClient -ArgumentList ($azProfile)
$token = $profileClient.AcquireAccessToken($azContext.Tenant.Id)
$authHeader = @{
    'Content-Type'='application/json'
    'Authorization'='Bearer ' + $token.AccessToken
}
Invoke-RestMethod -Method Post -Uri $uri -UseBasicParsing -Headers $authHeader

Microsoft SQL – Get latest backup time

 SELECT  name ,
            recovery_model_desc ,
            state_desc ,
            d AS 'Last Full Backup' ,
            i AS 'Last Differential Backup' ,
            l AS 'Last log Backup'
    FROM    ( SELECT    db.name ,
                        db.state_desc ,
                        db.recovery_model_desc ,
                        type ,
                        backup_finish_date
              FROM      master.sys.databases db
                        LEFT OUTER JOIN msdb.dbo.backupset a ON a.database_name = db.name
            ) AS Sourcetable 
        PIVOT 
            ( MAX(backup_finish_date) FOR type IN ( D, I, L ) ) AS MostRecentBackup