paschott

import-module dbatools
import-module ImportExcel

$servers="server1"
$date = get-date #format yyyymmdd

foreach ($server in $servers) {
	$ExcelFile = "C:\path\$server_Users_And_Logins_$date.xlsx"

	Get-DbaDbRoleMember -SqlInstance $server | Export-Excel -Path $ExcelFile -WorksheetName UserRoleMembers -AutoSize -AutoFilter -ExcludeProperty @("ItemArray", "RowError", "RowState", "Table", "HasErrors") -BoldTopRow

paschott

Import-Module dbatools
Import-Module sqlserver #used for Invoke-SqlCmd to run scripts w/ the "GO" batch separator

$CurrentDate = (Get-Date).ToString("yyyyMMdd_hhmmss")

$Servername = "localhost"
$databases = 'CSV_Set_Of_DB_Names'


#Get permissions for databases

paschott

SELECT
    objects.name AS Table_name,
    indexes.name AS Index_name,
    dm_db_index_usage_stats.user_seeks,
    dm_db_index_usage_stats.user_scans,
    dm_db_index_usage_stats.user_updates
FROM
    sys.dm_db_index_usage_stats
    INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID
    INNER JOIN sys.indexes ON indexes.index_id = dm_db_index_usage_stats.index_id AND dm_db_index_usage_stats.OBJECT_ID = indexes.OBJECT_ID

paschott

--Get SQL Agent Job names for SSRS Subscriptions
SELECT Schedule.ScheduleID AS JobName,
       [Catalog].Name AS ReportName,
       Subscriptions.Description AS Recipients,
       [Catalog].Path AS ReportPath,
       StartDate,
       Schedule.LastRunTime
  FROM [ReportServer].dbo.ReportSchedule
 INNER JOIN [ReportServer].dbo.Schedule
    ON ReportSchedule.ScheduleID     = Schedule.ScheduleID

paschott

#requires dbatools

$server = "servername"
$localCertPath = "C:\CertificateRequests"
$remoteCertPath = "C:\CertificateRequest\"

$adminuser = Import-Clixml C:\user.cred #stored credentials to access remote server

# Generate the CSR and download locally
$session = New-PSSession $server -Credential $adminuser

paschott

#Create new Certificate Request for SQL Server security
# Should be made into a function at some point
# Needs to be able to handle Cluster names/IP addresses

#Set location of the server
$Location = "City"
$State = "State"
$OU = "OU"
$Company = "Organization"

paschott

use SSISDB
GO

CREATE PROC dbo.usp_PurgeSSISCatalogLogs
	@RowsToDelete int = 5000
AS
BEGIN --Proc
/*
	Script name: Purge SSIS Catalog log tables
	Author: Tim Mitchell (www.TimMitchell.net)

paschott

#Note - will want to install "Chocolatey" first in order to use this file to install software.
# https://chocolatey.org/docs/installation
# Open a command prompt (cmd) and run the following:
#    @"%SystemRoot%\System32\WindowsPowerShell\v1.0\powershell.exe" -NoProfile -InputFormat None -ExecutionPolicy Bypass -Command "iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))" && SET "PATH=%PATH%;%ALLUSERSPROFILE%\chocolatey\bin"
# ..
# or open a PowerShell Admin prompt and run:
#  Set-ExecutionPolicy Bypass -Scope Process -Force; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))


# After Chocolatey is installed, open a PS prompt in Administrator mode,

paschott

DECLARE @Time TIME,
        @RowsToDelete INT,
        @PurgeDataPriorToDate DATETIME2,
        @RowsDeleted INT,
        @EndLoopTime TIME,
        @StartLoopTime TIME
SELECT @RowsToDelete = 5000
SELECT @EndLoopTime = '20:00:00'
SELECT @StartLoopTime = '08:00:00'

paschott

--TableName:Backup_MostRecent
;with
db as (
	select [Instance] = @@SERVERNAME,
		[Database]  = name,
	    [RecoveryMode]   = DATABASEPROPERTYEX(name, 'Recovery'),
	    [CreationTime]   = crdate,
	    [Status]         = DATABASEPROPERTYEX(name, 'Status')
		from master..sysdatabases
		where name!='tempdb'