PowerShell: Definitions

What does it mean? Start here, and for my own reference:

$PSScriptRoot
  # <a href="http://stackoverflow.com/questions/3667238/how-can-i-get-the-file-system-location-of-a-powershell-script">This</a> will tell me the root path of the currently running module or file.
Set-StrictMode -Version "Latest"
  # Use the latest version of PowerShell.

I have a TIPs.ps1 file which has a Break at the top preventing mistakes.

Break
  # Put at the top of a work script to prevent ourselves from running this entire script.

Another Break tip when used in a TRY CATCH.

Break # to just exit the process without throwing an error back to the error stream.

Generally I like to Throw my errors so processing stops.

try 
{
  Get-Content "c:\nonexistingfile.txt" -ErrorAction stop
} 
catch 
{
  throw 'Custom error message' # or $_
}

SQL Metadata load all databases all column data into a table

We had the idea to put all column data into a table for code review comparisons etc. Chris Trump came up with this. Thanks, Chris!

EXECUTE master.sys.sp_MSforeachdb @command1 = N'
USE [?];
 
INSERT INTO dbo.Database_DataDictionary (DATABASE_NAME, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, DATETIME_PRECISION)
SELECT  
        "?" as DATABASE_NAME,
        t.TABLE_NAME,
        c.COLUMN_NAME,
        c.ORDINAL_POSITION,
        UPPER(c.DATA_TYPE) as DATA_TYPE,
        ISNULL(c.CHARACTER_MAXIMUM_LENGTH,'''') AS CHARACTER_MAXIMUM_LENGTH,
        ISNULL(c.NUMERIC_PRECISION,'''') AS NUMERIC_PRECISION,
        ISNULL(c.NUMERIC_SCALE,'''') AS NUMERIC_SCALE,
        ISNULL(c.DATETIME_PRECISION,'''') AS DATETIME_PRECISION
 
FROM         
        INFORMATION_SCHEMA.COLUMNS AS c 
        INNER JOIN INFORMATION_SCHEMA.TABLES AS t ON 
                t.TABLE_NAME = c.TABLE_NAME
WHERE         
        t.TABLE_TYPE = ''Base Table'''
 

SELECT * FROM dbo.Database_DataDictionary

POWERSHELL: A wrapper for Get-ADGroupMember

I just wanted a wrapper for Get-ADGroup and Get-ADGroupMember to return all members of a group.

function Get-ADGroupMembers {
	<#
		.SYNOPSIS
			For any Get-ADGroup Get-ADGroupMember's Get-ADUser properties and output our PSObject

		.DESCRIPTION
			For any AD group return a list of members including members of subgroups.

		.PARAMETER  Group
			The AD Group name to find members for (without the DOMAIN).
 
		.EXAMPLE
			Get-ADGroupMembers -Group "SQL DBA"

		.EXAMPLE
			$Group = "SQL DBA"
			$Output = Get-ADGroupMembers -Group $Group
			$Output |Sort-Object LastName |Select-Object ObjectClass, GroupName, LastName, GivenName, SamAccountName, Name |ft -AutoSize
			Write-Host ("{0} Members found in AD group: {1}" -f $Output.Count, $Group)
			
		.INPUTS
			System.String 

		.OUTPUTS
			PSObject

		.NOTES
			20170113 Created

 

	#>
	[CmdletBinding()] 
	param(
		[Parameter(Position=0, Mandatory=$true)]
		[ValidateNotNullOrEmpty()]
		[System.String]
		$Group 
	)
	try {
				
		#
		## Get all 
		#  
		
		$Groups = Get-ADGroup -Identity $Group 	 
		$output = ForEach ($g in $groups)  {
			$results = Get-ADGroupMember -Identity $g.name -Recursive | Get-ADUser -Properties displayname, objectclass, name 

			ForEach ($r in $results){
				New-Object PSObject -Property @{
				    GroupName = $g.Name
				    Username = $r.Name
				    ObjectClass = $r.ObjectClass
				    Name = $r.Name
					GivenName = $r.GivenName
					LastName = $r.Surname
					Enabled = $r.Enabled
					SamAccountName = $r.SamAccountName 
				 }
			}
		} 
	  Write-Output $output
	}
	catch {
		throw
	}
}

POWERSHELL: Get all sysadmins on a SQL Server

I use this to retrieve all SQL sysadmins on a server, and drill down, if any are Active Directory groups, to the individuals.


$SQLInstance = "SQLDEV";
$DomainFilter = "MYCOMPANYDOMAIN"

$Server = new-object Microsoft.SqlServer.Management.Smo.Server $SQLInstance;
$SQLLogins = $Server.Logins;

$sysadmins = $null;
$sysadmins = foreach($sysadmin in $SQLLogins)  {
	foreach($role in $sysadmin.ListMembers()) {
		if($role -match 'sysadmin') {
			Write-Verbose "sysadmin found: $($sysadmin.Name)" 
			$sysadmin | Select-Object `
				@{label = "SQLServer"; Expression = {$SQLInstance}}, `
				@{label = "CurrentDate"; Expression = {(Get-Date).ToString("yyyy-MM-dd")}}, `
				Name, LoginType, CreateDate, DateLastModified;
			$sysadmin |?{$_.LoginType -eq 'WindowsGroup' -and $_.Name -match '$DomainFilter'} |%{ 
				Get-ADGroupMembers -Group $_.Name.Replace("$DomainFilter\",'') |Sort-Object LastName | Select-Object `
						@{label = "SQLServer"; Expression = {$SQLInstance}}, `
						@{label = "CurrentDate"; Expression = {(Get-Date).ToString("yyyy-MM-dd")}}, `
						Name,@{label = "LoginType"; Expression = {'GroupUser'}}
	 
	}
		};
	};
};

$sysadmins | ft -AutoSize

Report all users in server databases with dbo permissions

This is from an idea from here.

IF OBJECT_ID('tempdb.dbo.#t1') IS NOT NULL
	DROP TABLE #t1

SELECT  CAST('?' AS VARCHAR(128)) AS [Database Name],
        [su1].[name] AS [Database User Name],
        [su2].[name] AS [Database Role]
INTO    [#t1]
FROM    [sys].[database_role_members] [r]
INNER JOIN .[sysusers] [su1]
ON      [su1].[uid] = [r].[member_principal_id]
INNER JOIN .[sysusers] [su2]
ON      [su2].[uid] = [r].[role_principal_id]
WHERE   [su2].[name] IN ('db_owner')
        AND [su1].[name] NOT IN ('dbo')
        AND 1 = 2

INSERT [#t1]
        ([Database Name],
		[Database User Name],
         [Database Role]
        ) 
EXEC sp_MSForEachDB 'SELECT ''?'' AS [Database Name], su1.name AS [Database User Name], su2.name AS [Database Role]
						FROM [?].sys.database_role_members r
							INNER JOIN [?]..sysusers su1 ON su1.[uid] = r.member_principal_id
							INNER JOIN [?]..sysusers su2 ON su2.[uid] = r.role_principal_id
						WHERE su2.name IN(''db_owner'') AND su1.name NOT IN(''dbo'')'
DELETE
FROM [#t1]
WHERE [Database Name] IS NULL

SELECT [Database Name],
       [Database User Name],
       [Database Role]
FROM [#t1]

GO

Report on SQLAgent role members

Just remove the filter to see everyone.


;WITH    [RoleMembers]([member_principal_id], [role_principal_id])
          AS (
              SELECT    [rm1].[member_principal_id],
                        [rm1].[role_principal_id]
              FROM      [msdb].[sys].[database_role_members] [rm1] (NOLOCK)
              UNION ALL
              SELECT    [d].[member_principal_id],
                        [rm].[role_principal_id]
              FROM      [msdb].[sys].[database_role_members] [rm] (NOLOCK)
              INNER JOIN [RoleMembers] AS [d]
              ON        [rm].[member_principal_id] = [d].[role_principal_id]
             )
    SELECT DISTINCT
            [rp].[name] AS [database_role],
            [mp].[name] AS [database_user],
            [mp].[type]
    FROM    [RoleMembers] [drm]
    JOIN    [msdb].[sys].[database_principals] [rp]
    ON      ([drm].[role_principal_id] = [rp].[principal_id])
    JOIN    [msdb].[sys].[database_principals] [mp]
    ON      ([drm].[member_principal_id] = [mp].[principal_id])
    WHERE   [rp].[name] LIKE 'SQLAgent%'
            AND [mp].[name] NOT LIKE '##%'
    ORDER BY [rp].[name]


This should produce output similar to this:

 or

database_role	database_user	type
SQLAgentOperatorRole	PolicyAdministratorRole	R
SQLAgentReaderRole	PolicyAdministratorRole	R
SQLAgentReaderRole	SQLAgentOperatorRole	R
SQLAgentUserRole	dc_admin	R
SQLAgentUserRole	dc_operator	R
SQLAgentUserRole	MS_DataCollectorInternalUser	S
SQLAgentUserRole	PolicyAdministratorRole	R
SQLAgentUserRole	SQLAgentOperatorRole	R
SQLAgentUserRole	SQLAgentReaderRole	R

SHOWPLAN permissions

One can be database owner, SHOWPLAN within the database, CONTROL Database, CONTROL Server, or ALTER TRACE at the server level.

See Rob Farley’s article for a script to run a test.

Rob Farley : SHOWPLAN permission denied even if the database isn’t actually used

To view a query plan, you need SHOWPLAN permission on the database level at least. You have this if you have CONTROL DATABASE, or CONTROL SERVER, or if you have ALTER TRACE at the instance level. I know this last one because it’s mentioned in Books Online

Source: sqlblog.com/blogs/rob_farley/archive/2015/04/14/showplan-permission-denied-even-if-the-database-isn-t-actually-used.aspx

ERROR: SSPI handshake failed with error code … Error: 18452

We were trying to connect a remote SQL publisher to a local subscriber and on the subscriber get these errors, found in the SQL Errorlog.

Date		12/8/2016 6:52:42 AM
Log		SQL Server (Current - 12/7/2016 6:03:00 PM)

Source		Logon

Message
Error: 17806, Severity: 20, State: 14.

----

Date 12/8/2016 6:52:42 AM
Log SQL Server (Current - 12/7/2016 6:03:00 PM)

Source Logon

Message
SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed [CLIENT: 172.31.5.158]

----
Date		12/8/2016 6:52:42 AM
Log		SQL Server (Current - 12/7/2016 6:03:00 PM)

Source		Logon

Message
Error: 18452, Severity: 14, State: 1.

----
Date		12/8/2016 6:52:42 AM
Log		SQL Server (Current - 12/7/2016 6:03:00 PM)

Source		Logon

Message
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: xxx.xx.x.xxx]

----

However, the SQL login was coming from a domain that already HAD replication running on another database.

It turned out to be an incorrect login name, rather than an untrusted domain.

Never trust an error message.