SQL Metadata load all databases all column data into a table

\ by

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
Posted in: Coding, SQL

POWERSHELL: A wrapper for Get-ADGroupMember

\ by

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
	}
}
Posted in: Coding, Permissions, PowerShell

POWERSHELL: Get all sysadmins on a SQL Server

\ by

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
Posted in: Coding, Permissions, PowerShell