tcartwright

<# download and install Solarwinds SentryOne Plan Explorer #>
$filenamePlanExplorerInstall = "$env:USERPROFILE\downloads\" + ([string](Get-Date -format "yyyy-MM-dd")) + "_SolarWinds-PlanExplorer.exe"
Start-BitsTransfer -Source 'https://downloads.solarwinds.com/solarwinds/Release/FreeTool/SolarWinds-PlanExplorer.exe' -Destination $filenamePlanExplorerInstall
& $filenamePlanExplorerInstall /install /passive /norestart

tcartwright

<#
.SYNOPSIS
Generates T-SQL scripts to enable temporal or history tracking for all user tables in a SQL Server database.

.DESCRIPTION
This PowerShell script connects to a specified SQL Server and database, detects the SQL Server version,
and generates T-SQL to enable historical tracking of data changes.

- For SQL Server **2016 or later** (version 13+):
  - The script generates ALTER TABLE statements to enable **system-versioned temporal tables**.

tcartwright

<#
.SYNOPSIS
    Generates a T-SQL script that adds 'ModifiedBy' and 'ModifiedDate' columns to all user tables in a SQL Server database that do not already have them.

.DESCRIPTION
    This script connects to the specified SQL Server and scans for user tables missing the 'ModifiedBy' and 'ModifiedDate' columns.
    It then generates a SQL script that:
      - Adds these columns with default values.
      - Creates AFTER UPDATE triggers to automatically populate these fields.

tcartwright

SELECT CURRENT_USER AS [CURRENT_USER],      /*Returns the name of the current security context. If EXECUTE AS has been used, it returns the impersonated user's name. */
    USER AS [USER],                         /*Similar to USER_NAME(), it returns the database user name and, after EXECUTE AS, the impersonated user. */ 
    USER_NAME() AS [USER_NAME()],           /*Returns the database user name based on the provided ID. If no ID is specified, it returns the current user's name. It also returns the impersonated user's name after EXECUTE AS. */
    SESSION_USER AS [SESSION_USER],         /*Returns the name of the user who initiated the session, even if EXECUTE AS has been used. */
    SYSTEM_USER AS [SYSTEM_USER],           /*Returns the name of the current security context. If EXECUTE AS has been used, it returns the impersonated user's name. */
    SUSER_NAME() AS [SUSER_NAME()],         /*Returns the login name. When called with a server principal ID, it returns the name associated with that ID. */
    ORI

tcartwright

param (
    [string]$JwksUrl = "https://sso.server.com/.well-known/openid-configuration/jwks"
)

Clear-Host

try {
    Write-Host "Fetching JWKS from $JwksUrl ..."
    $jwksJson = Invoke-RestMethod -Uri $JwksUrl -UseBasicParsing

tcartwright

Claims-Based Authentication with Roles, Negations, and Custom Claims

This approach uses claims-based authentication for the API, with roles as a convenient way to manage claims.

Each user or authenticating entity can be assigned one or more roles, and each role maps to a predefined set of claims that represent specific permissions (e.g., can_view_reports, can_edit_users, etc.).

Advanced Features

Two advanced features will be supported:

tcartwright

/* JUST CHANGE THE CONTENTS OF THE @tsql variable to your query. It does not even need to produce rows */

DECLARE @tsql NVARCHAR(MAX) = N'
	SELECT TOP 10 [t].[name],
		[t].[object_id],
		[t].[schema_id],
		[t].[type],
		[t].[type_desc],
		[t].[create_date]
	FROM sys.[tables] AS [t] 

tcartwright

%windir%\system32\inetsrv\appcmd.exe set apppool "POOL_NAME" -startMode:AlwaysRunning
%windir%\system32\inetsrv\appcmd.exe set apppool "POOL_NAME" -processModel.idleTimeout:00:00:00
%windir%\system32\inetsrv\appcmd.exe set apppool "POOL_NAME" -processModel.idleTimeoutAction:Suspend
%windir%\system32\inetsrv\appcmd.exe set app "SITE_NAME/" -preloadEnabled:true
%windir%\system32\inetsrv\appcmd.exe set site "SITE_NAME" -applicationDefaults.preloadEnabled:true

tcartwright

--SELECT [sp].[name], [sp].[type_desc] FROM sys.[server_principals] AS [sp] WHERE type_desc LIKE 'windows%' ORDER BY [sp].[name]

-- SELECT * FROM sys.[server_principals] AS [sp] WHERE type_desc = 'WINDOWS_LOGIN' AND name LIKE '%.%'
DECLARE @username sysname = '****',
    @role_name sysname = 'db_owner'

IF NOT EXISTS (SELECT * FROM sys.[server_principals] AS [sp] WHERE [sp].[name] = @username) BEGIN
    RAISERROR('The user [%s] does not exist as a server login.', 16, 10, @username)
    RETURN;
END

tcartwright

Clear-Host

# UPDATE QUERY / SERVER / DB
$serverInstance = "..."
$database = "..."
$query = "
  SELECT * FROM sys.tables 
"
# UPDATE QUERY / SERVER / DB