Turn off AutoShrink on databases via powershell

Easy peasy script to turn off AutoShrink on all databases.  I’ll let Paul Randal do the talking as to why you shouldn’t have autoshrink turned on.

Import-Module sqlps -DisableNameChecking

$ignoreDBs = @('IgnoreDB')

try{
    gc -Path 'c:\Servers.txt' | %{
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $_
        $srv.Databases | where{-not $_.IsSystemObject -and $_.Name -notin $ignoreDBs} | %{
            if($_.AutoShrink -eq $true){
                $_.AutoShrink = $false
                $_.Alter();
            }
        }        
    }
}
catch{
    $srvName
    $_ | fl -Force
}
finally{

}

Expand Windows Groups on Sql Server

Quick post on how to expand windows groups to show their sub-groups and logins in sql server.  This just writes the group hierarchy to the console.  Use at your own risk.

import-module activedirectory

function Get-GroupHierarchy{
    param(
        [Parameter(Mandatory=$true)]
        [String]$searchGroup
    )
    $outputs = @();
    [int]$i++ | out-null;

    get-adgroupmember $searchGroup | sort-object objectClass -descending | %{
        $output = new-object -TypeName PSObject -Property @{
            Parent = $searchGroup
            GroupName = $_.Name
            Type = $_.objectClass
            Hierarchy = $i
        }
        $outputs += $output

        if($_.ObjectClass -eq 'group'){
            $outputs += Get-GroupHierarchy $_.name
        }
    }
    return $outputs;
}

cls
$srvName = 'ServerName'
$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
$srvConn.ServerInstance = $srvName
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn
$ignoreGroups = @('NT SERVICE\MSSQLSERVER', 'NT SERVICE\SQLSERVERAGENT');

$srv.Logins | where{$_.LoginType -eq [Microsoft.SqlServer.Management.Smo.LoginType]::WindowsGroup -and $_.Name -notin $ignoreGroups} | %{
    $loginName = $_.Name.Replace('TRX0\', '')
    Write-Host "Windows Group:  $loginName" -ForegroundColor Green
    Get-GroupHierarchy $loginName | ft -AutoSize   
    Write-Host "`n`r"
}

Calling sp_start_job via Linked Server

If you’re trying to execute sp_start_job via a linked server and keep getting the error:

Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 67
The specified @job_name (‘InsertJobNameHere’) does not exist.

,then here are the steps you need to follow to resolve the issue:

  1. First, take stock of your life and ask yourself why you’re doing this in the first place; use an SSIS package man!
  2. Find the login that is being used for the linked server.
  3. Really, really question whether this is the best option for accomplishing the task you’re trying to solve.
  4. On the target server, add the login from step 2 to the msdb database on the target server.
  5. Add the login to the SQLAgentOperatorRole & TargetServersRole on the target server.
  6. Grant EXEC to sp_start_job stored procedure in MSDB to the TargetServersRole on the target server.
USE [msdb]
GO
CREATE USER [linkedServerLogin] FOR LOGIN [linkedServerLogin]
GO
USE [msdb]
GO
EXEC sp_addrolemember N'TargetServersRole', N'linkedServerLogin'
GO
USE [msdb]
GO
EXEC sp_addrolemember N'SQLAgentOperatorRole', N'linkedServerLogin'
GO
GRANT EXEC ON sp_start_job TO [linkedServerLogin]

Drop Linked Server from All Servers

Quick script on how to drop a linked server from a list of servers.  Minimally tested, use at your own risk.

 $LinkedServerName = 'LinkedServerToFindAndDrop'

try{
    gc -Path c:\Servers.txt | %{

        $srvName = $_
	    $srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
	    $srvConn.ServerInstance = $srvName
	    $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn

        $linkedServer = $srv.LinkedServers | where{$_.DataSource -eq $LinkedServerName} 
        if($linkedServer -ne $null){
            for([int]$i = 0; $i -lt $linkedServer.LinkedServerLogins.Count; $i++){
                $login = $linkedServer.LinkedServerLogins.item($i);
                $login.Drop();
            }

            $linkedServer.Drop();
        }
    }

}
catch{
    $_ | fl -Force
} 

Scientific Notation and the ISNUMERIC function

Fun problem at work today.  Someone was importing some data, and the value 2d4 kept evaluating as 1 using the ISNUMERIC function.  The reason why this evaluates true, is because 2d4 is technically a number using scientific notation.  The ‘d’ stands for double-precision.  2e4 would also return 1 (exponential value), as would –12, +12, or $12.

In order to correctly check to see if the characters are numeric (in this case, the first 3 characters of a string) you could use this:

DECLARE @Test NVARCHAR(100) = '2D4jlkafkjl'

SELECT 
	CASE
	WHEN LEFT(@Test,3) LIKE '[0-9][0-9][0-9]' THEN 
		CAST(LEFT(@Test, 3) AS INT)
ELSE
999
END 

Alternatively, if you want to check to see if a string has any letters at all in it, you could just do this:

DECLARE @Test NVARCHAR(100) = '11i111'

SELECT 
	CASE
	WHEN @Test NOT LIKE '%[^0-9]%' THEN 'all numbers'
ELSE
'has letters'
END 

Associate User & Logins in Sql Server via Powershell

Quick script to re-associate logins with users that have the same name.  If the user in the database is named the same as the login and has no login currently associated with it, it will set the database user to use the login with the same name.  Apparently, you can’t do this directly via powershell by setting the users’ login to the login name, as it errors out with “Modifying the Login property of the User object is not allowed. You must drop and recreate the object with the desired property.”  Hence the SQL call to sync up the user.

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$serverName = 'ServerName'

try{
	$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
	$srvConn.ServerInstance = $serverName
	$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn
	
	$srv.Logins | where{$_.LoginType -eq [Microsoft.SqlServer.Management.Smo.LoginType]::SqlLogin} | %{
		$login = $_
		$srv.Databases | %{
			if($_.Users.Contains($login.Name)){
				$user = $_.Users[$login.Name];
				if($user.Login -eq ''){
<#					#can't do this apparently, smo will only let you drop & re-create...
					$user.Login = $login.Name;
					$user.Alter();
#>
					$_.ExecuteNonQuery("sp_change_users_login 'auto_fix', '" + $user.Name + "'")
				}
			}
		}
	}
}
catch{
	$_ | fl -Force
}

Add a Database Role and Grant EXEC

Quick post on how to add a database role to all your databases and grant EXEC to said role.  This will also add the specified user to said role (aptly named db_exec).  Use with caution, minimally tested.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
 
$servers = @('ServerName')
$roleName = 'db_exec'
$LoginName = 'LoginName'; 

 
 try{
	foreach($server in $servers){
	 
	    $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
	 
	    if (-not $srv.Logins.Contains($LoginName)){ 
	       throw "Login $LoginName does not exist on server $server"
		   return;
		}
		
	    $login = $srv.Logins[$LoginName]
	 
	    foreach($database in $srv.Databases | where{-not $_.IsSystemobject -and $ignoreDBs -notcontains $_.Name -and $_.Status -eq [Microsoft.SqlServer.Management.Smo.DatabaseStatus]::Normal}){
		
			if(-not $database.Users.Contains($login.Name)){
				$user = New-Object('Microsoft.SqlServer.Management.Smo.User') $database, $login.Name
				$user.Login = $login.Name
	        	$user.create();
			}
			
			$user = $database.Users[$LoginName]
			
			if(-not $database.Roles.Contains($roleName)){
				$role = New-Object('Microsoft.SqlServer.Management.smo.DatabaseRole') $database, $roleName
				$role.Create();
			}
			
			$role = $database.Roles[$roleName]
			$role.AddMember($user.Name);
			
			$objDbPerms = New-Object Microsoft.SqlServer.Management.Smo.DatabasePermissionSet 
			$objDbPerms.Execute = $true
			$database.Grant($objDbPerms, $roleName);
			$database.Alter();
	    }
	}
}
catch{
	$_ | fl -Force
}

Change Column Length via Powershell

Changing the length of a column is not as straightforward as you would think.  There is no .Length property on the smo column object itself, rather you have to set the column.property[‘Length’].value to whatever length you are setting.

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

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

try{
	$servers | %{
		$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
		$srvConn.ServerInstance = $_
		$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn
		
		$srv.databases | where{$_.Status -eq [Microsoft.SqlServer.Management.Smo.DatabaseStatus]::Normal} | %{
			$dbName = $_.Name
			$_.Tables | where{$_.Name -eq "TableName" -and $_.Schema -eq "dbo"} | %{
				$tableName = $_.Name
				$_.Columns | where{$_.Name -eq 'ColumnName'} | %{
					if([int]$_.Properties['Length'].value -eq 100){
						$_.Properties['Length'].value = 500
						$_.Alter();
					}
				}
			}
		}
		
	}
}
catch{
	$_ | fl -Force
}

Find database last access date via powershell

Bit of misnomer, it’s still using SQL executed via the dm_db_index_usage_stats dmv to get the last user seek\scan\lookup\update column to determine the last access time, but still a good script to get a list of the last usage dates for all your databases on all your servers.  Also includes the last server restart time, as the dm_db_index_usage_stats resets upon server restart.  Use at your own risk.

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

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

$outputs = @();

$lastAccessSQL = "
SELECT 
	DB_NAME(database_id) AS DatabaseName,
	COALESCE(
		MAX(last_user_seek),
		MAX(last_user_scan),
		MAX(last_user_lookup),
		MAX(last_user_update),
		'1/1/1900') AS LastAccessDate,
		(SELECT create_date FROM sys.databases WHERE name = 'tempdb') AS LastServerRestart
FROM
	sys.dm_db_index_usage_stats
WHERE DB_NAME(database_id) NOT IN('tempdb', 'master', 'msdb')
GROUP BY database_id
ORDER BY DatabaseName
"

$servers | %{
	$srvName = $_
	$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
	$srvConn.ServerInstance = $srvName

	$dr = $srvConn.ExecuteReader($lastAccessSQL)

	while($dr.Read()){
		$output = New-Object -TypeName PSObject -Property @{
		    ServerName = $srvName
		    DatabaseName = $dr.GetString(0)
			LastAccessDate = $dr.GetDateTime(1)
			ServerLastRestart = $dr.GetDateTime(2)
		}
		$outputs += $output
	}
	
}
$outputs | SELECT ServerName, DatabaseName, LastAccessDate, ServerLastRestart | Out-GridView

Add a column to a table via powershell

Quick post on how to add a column to a table via powershell.  I had a need to blast a new column onto a bunch of databases on a bunch of servers.  Powershell makes this too easy.  Use at your own risk. 

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$servers = Get-Content c:\Servers.txt
$tableName = 'TableName'
$schema = 'dbo'
$columnName = 'ColumnName'

try{

	$servers | %{
		$srvName = $_
		$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
		$srvConn.ServerInstance = $srvName
		$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn

		$srv.Databases | where{$_.Status -eq [Microsoft.SqlServer.Management.Smo.DatabaseStatus]::Normal} | %{
			$dbName = $_.Name
			$table = $_.Tables | where{$_.Name -eq $tableName -and $_.Schema -eq $schema}
			if($table -ne $null){
				$column = $table.Columns | where{$_.Name -eq $columnName}
				if($column -eq $null){  #add the column if it does not exist
					$column = New-Object Microsoft.SqlServer.Management.Smo.Column
					$column.Parent = $table
					$column.Name = $columnName
					$column.DataType = [Microsoft.SqlServer.Management.Smo.DataType]::DateTime
					$column.Create();
				}
			}
		}

	}

}
catch{
	$_ | fl -Force
}