[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$Error.Clear()
cls

$servers = Get-Content c:\Test\Servers.txt
$logins = @("aaLogin","TestLogin")

foreach($server in $servers)
{
	$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
	
	#drop database users
	foreach($database in $srv.Databases)
	{
		foreach($login in $logins)
		{
			if($database.Users.Contains($login))
			{
				$database.Users[$login].Drop();
			}
		}
	}
	
	#drop server logins
	foreach($login in $logins)
	{
		if ($srv.Logins.Contains($login)) 
		{ 
			$srv.Logins[$login].Drop(); 
		}
	}
}

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$Error.Clear()
cls

$servers = Get-Content c:\Test\Servers.txt
$roles = @("db_datareader","db_datawriter")

[string]$LoginName = "aaLogin"; 
[string]$Password = "BenderIsGreat!&";

foreach($server in $servers)
{

	$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server

	#careful, if the login exists this will drop it
	if ($srv.Logins.Contains($LoginName)) 
	{ 
		$srv.Logins[$LoginName].Drop(); 
	}

	$login = New-Object ('Microsoft.SqlServer.Management.Smo.Login') $srv, $LoginName 
	$login.LoginType = [Microsoft.SqlServer.Management.Smo.LoginType]::SqlLogin; 
	$login.Create($Password);

	# refresh login so it contains the SID
	$srv.Logins.Refresh(); 
	$login = $srv.Logins[$LoginName]

	foreach($database in $srv.Databases)
	{
		if($database.IsSystemObject -eq $false -and $database.Name -ne "LiteSpeedLocal")
		{
			#careful, this will drop the user from the db if it exists
			if($database.Users.Contains($LoginName))
			{
				$database.Users[$LoginName].Drop();
			}
		
			$user = New-Object('Microsoft.SqlServer.Management.Smo.User') $database, $login.Name
			$user.Login = $login.Name
			$user.create();
			
			foreach($role in $roles)
			{
				$role = $database.Roles[$role]	
				$role.AddMember($user.Name)
			}
			
			foreach($proc in $database.StoredProcedures | where{$_.IsSystemObject -eq $false})
			{
				$perm = New-Object('Microsoft.SqlServer.Management.Smo.ObjectPermissionSet')
				$perm.ViewDefinition = $true;
				$proc.grant($perm, $LoginName);
			}
			
			foreach($view in $database.Views | where{$_.IsSystemObject -eq $false})
			{
				$perm = New-Object('Microsoft.SqlServer.Management.Smo.ObjectPermissionSet')
				$perm.ViewDefinition = $true;
				$view.grant($perm, $LoginName);
			}
			
			foreach($udf in $database.UserDefinedFunctions | where{$_.IsSystemObject -eq $false})
			{
				$perm = New-Object('Microsoft.SqlServer.Management.Smo.ObjectPermissionSet')
				$perm.ViewDefinition = $true;
				$udf.grant($perm, $LoginName);
			}
			
			foreach($uda in $database.UserDefinedAggregates | where{$_.IsSystemObject -eq $false})
			{
				$perm = New-Object('Microsoft.SqlServer.Management.Smo.ObjectPermissionSet')
				$perm.ViewDefinition = $true;
				$uda.grant($perm, $LoginName);
			}
			
			foreach($trigger in $database.Triggers | where{$_.IsSystemObject -eq $false})
			{
				$perm = New-Object('Microsoft.SqlServer.Management.Smo.ObjectPermissionSet')
				$perm.ViewDefinition = $true;
				$trigger.grant($perm, $LoginName);
			}
			
			
		}
	}

}

cls
$servers = Get-Content c:\Test\Servers.txt
$countThreshold = 500

foreach($server in $servers)
{
	$rows = Get-SqlData -dbname msdb -sqlserver $server -qry "SELECT COUNT(*) AS HistCount FROM dbo.sysjobhistory" #| Format-Table -AutoSize
	$nRowCount = [int]$rows[0]
	if($nRowCount -ge $countThreshold)
	{
		Write-Host "Server $server has a rowcount of $nRowCount in sysjobhistory.  There is probably no history cleanup job on this server."
	}
}

cls
$servers = Get-Content c:\Test\Servers.txt

foreach($server in $servers)
{
	$hashist = $false
	$jobs = Get-AgentJob $server
	foreach($job in $jobs)
	{
		#Write-Host $job.Name
		if($job.Name -like "*clean*" -and $job.Name -like "*hist*" -and $job.Name -notlike "*distribution*")
		{
			$hashist = $true;
			break;
		}
	}
	if($hashist -eq $false)
	{
		Write-Host "Server $server does not have a history clean up job"
	}
}