PowerShell Script to check SQL server connectivity, version, custering status, user permissions

SharePoint service account requires dbcreator and securityadmin roles to setup a farm. Often time it is neglected by the clients while provisioning the accounts as in most cases there are two different teams involved in this the Active Directory team and the DBA’s whose manages the SQL Server platform.

The missing right are discovered late in stage when we are about to begin our installation which is a show stopper and impacts the timeline. Some organization have policies and procedures in order to get these type of rights assigned and the DBA’s would have all sort of question which they did not bother to ask when the requirements were laid down.

Usually I follow a check list of things to be checked before we go ahead and perform the installation and configuration. As far as SQL Server is concerned we need to check for the following things

1. Is SQL Server reachable. Essentially we want to check if our WFE/APP server are able to talk to SQL server traversing through firewalls. This is one of the most common problem in big enterprise with complex environment.
2. Is there a SQL Server login created for the service account.
3. SQL Server version
4. SQL Server clustering status
5. And finally, the rights for service account which should have dbcreator and securityadmin

In past I used a crude way or creating an ODBC DSN on WFE/APP servers to connect to the SQL Server which would help me validate first two items on my check list. There was no way easy way to retrieve SQL Server version and to check if the service account has been granted the dbcreator and securityadmin roles. So I was looking for a way to check user permissions on SQL Server. In my quest for solution I came across this article by Pinal Dave http://blog.sqlauthority.com/2009/04/14/sql-server-check-if-current-login-is-part-of-server-role-member/. This is what I needed but then we don’t have access to SQL Server or SQL Management studio to run the queries. So I though why not use PowerShell to do the job. So following below is script I came out with. This not only check for right for the service account but in a way also validated if SQL Server is accessible and extract SQL Server version. You can extract other useful server information refere to this MSDN article on other properties which you can extract http://msdn.microsoft.com/en-us/library/ms174396.aspx

#------------------------------------------------------------------------------------------------------------------------#
$currentUser = "$env:USERDOMAIN\$Env:USERNAME"
$sqlServer = "SQLDB"

$serverRolesToCheck = "dbcreator","securityadmin"

#$serverRolesToCheck = "sysadmin","dbcreator","diskadmin","processadmin",`
#					  "serveradmin","setupadmin","securityadmin","fake"

$objSQLConnection = New-Object System.Data.SqlClient.SqlConnection
$objSQLCommand = New-Object System.Data.SqlClient.SqlCommand

Try {

	$objSQLConnection.ConnectionString = "Server=$sqlServer;Integrated Security=SSPI;"
	Write-Host "Trying to connect to SQL Server instance on $sqlServer..." -NoNewline
	$objSQLConnection.Open() | Out-Null
	Write-Host "Success."

	$strCmdSvrDetails = "SELECT SERVERPROPERTY('productversion') as Version"
	$strCmdSvrDetails += ",SERVERPROPERTY('IsClustered') as Clustering"
	$objSQLCommand.CommandText = $strCmdSvrDetails
	$objSQLCommand.Connection = $objSQLConnection
	$objSQLDataReader = $objSQLCommand.ExecuteReader()
	if($objSQLDataReader.Read()){
		Write-Host ("SQL Server version is: {0}" -f $objSQLDataReader.GetValue(0))
		if ($objSQLDataReader.GetValue(1) -eq 1){
			Write-Host "This instance of SQL Server is clustered"
		} else {
			Write-Host "This instance of SQL Server is not clustered"
		}
	}
	$objSQLDataReader.Close()

	ForEach($serverRole in $serverRolesToCheck) {
		$objSQLCommand.CommandText = "SELECT IS_SRVROLEMEMBER('$serverRole')"
		$objSQLCommand.Connection = $objSQLConnection
		Write-Host "Check if $currentUser has $serverRole server role..." -NoNewline
		$objSQLDataReader = $objSQLCommand.ExecuteReader()
		if ($objSQLDataReader.Read() -and $objSQLDataReader.GetValue(0) -eq 1){
			Write-Host -BackgroundColor Green -ForegroundColor White "Pass"
		}
		elseif($objSQLDataReader.GetValue(0) -eq 0) {
			Write-Host -BackgroundColor Red -ForegroundColor White "Fail"
		}
		else {
			Write-Host -BackgroundColor Red -ForegroundColor White "Invalid Role"
		}
		$objSQLDataReader.Close()
	}

	$objSQLConnection.Close()
}
Catch {
	Write-Host -BackgroundColor Red -ForegroundColor White "Fail"
	$errText =  $Error[0].ToString()
	if ($errText.Contains("network-related")){
		Write-Host "Connection Error. Check server name, port, firewall."
	}
	elseif ($errText.Contains("Login failed")){
		Write-Host "Not able to login. SQL Server login not created."
	}
	Write-Host $errText
}