Skip to content

Instantly share code, notes, and snippets.

View dhmacher's full-sized avatar

Daniel Hutmacher dhmacher

View GitHub Profile
@dhmacher
dhmacher / Create_Agent_proxy_procedures.sql
Last active October 20, 2023 08:28
Create abstraction procedures for SQL Server Agent jobs
USE msdb;
GO
CREATE USER Agent_job_abstraction
WITHOUT LOGIN WITH DEFAULT_SCHEMA=dbo;
GO
ALTER ROLE SQLAgentOperatorRole
ADD MEMBER Agent_job_abstraction;
GO
CREATE OR ALTER PROCEDURE dbo.Create_Agent_proxy_procedures
@Principal_name sysname=N'Agent_job_abstraction',
@dhmacher
dhmacher / update-statistics.sql
Last active September 22, 2023 09:57
Use UPDATE STATISTICS to fake table & index sizes
DECLARE @object_id int=OBJECT_ID('dbo.tablename'),
@rowcount bigint=10000000;
SELECT N'UPDATE STATISTICS '+
--- Name of the table
QUOTENAME(OBJECT_SCHEMA_NAME(@object_id))+N'.'+QUOTENAME(OBJECT_NAME(@object_id))+
@dhmacher
dhmacher / script-server-principals.sql
Created September 13, 2023 12:15
Script out server-level principals and their memberships in fixed server roles.
DECLARE @principals TABLE (
seq int IDENTITY(1, 1) NOT NULL,
principal_id int NOT NULL,
[sql] nvarchar(max) NOT NULL,
PRIMARY KEY CLUSTERED (seq)
);
--- Windows logins and groups:
INSERT INTO @principals (principal_id, [sql])
SELECT sp.principal_id,
@dhmacher
dhmacher / make-sa-database-owner.sql
Last active September 7, 2023 08:42
Make sa the owner of all databases (and add the former owner to the db_owner role)
DECLARE @sql nvarchar(max)=N'';
SELECT @sql=@sql+N'
USE '+QUOTENAME(db.[name])+N';
IF (USER_ID('+QUOTENAME(sp.[name], N'''')+N') IS NULL)
CREATE USER '+QUOTENAME(sp.[name])+N' FOR LOGIN '+QUOTENAME(sp.[name])+N';
ALTER ROLE [db_owner] ADD MEMBER '+QUOTENAME(sp.[name])+N';
-- Source: https://sqlsunday.com/2017/02/21/finding-primary-key-candidates/
IF (OBJECT_ID('dbo.FindPrimaryKey') IS NULL)
EXEC('CREATE PROCEDURE dbo.FindPrimaryKey AS --');
GO
/*
This stored procedure is used to identify primary key candidates.
Copyright Daniel Hutmacher under Creative Commons 4.0 license with attribution.
@dhmacher
dhmacher / Agent job visualization.sql
Created October 22, 2021 08:37
Visualize agent jobs as a gannt chart
--- Read more: https://sqlsunday.com/2016/11/17/visual-representation-of-sql-server-agent-jobs/
/*
Copyright Daniel Hutmacher under Creative Commons 4.0 license with attribution.
http://creativecommons.org/licenses/by/4.0/
Source: http://sqlsunday.com/downloads/
DISCLAIMER: This script may not be suitable to run in a production
@dhmacher
dhmacher / Extract-WooCommerceOrders.ps1
Created October 12, 2021 13:12
Generates a SIE accounting file from WooCommerce sales orders.
# Provided as-is, without any warranty, implied or express.
# General:
$file = "Precon-" + (Get-Date).toString("yyyyMMdd-HHmmss") + ".se"
$baseUri = "https://example.com/wp-json/wc/v3/"
# WooCommerce authentication:
$key = "cs_aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"
$secret = "cs_aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"
$base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $key, $secret)))
@dhmacher
dhmacher / New-MastodonPost.ps1
Created December 19, 2022 07:01
Powershell function to post a simple, plaintext status to a Mastodon instance
<#
To create an access token,
* go to settings -> Development
* Click "New Application"
* Enter a name
* Allow "write:statuses"
* Click Submit
* Click on the new application to review the keys
* The "Access token" is the one you need
@dhmacher
dhmacher / New-PasswordLink.ps1
Created December 16, 2022 13:53
Powershell code to generate a password pusher URL (using pw.strd.co)
/*
Copyright Daniel Hutmacher under Creative Commons 4.0 license with attribution.
http://creativecommons.org/licenses/by/4.0/
Source: https://github.com/sqlsunday/sp_ctrl3
DISCLAIMER: This script may not be suitable to run in a production
environment. I cannot assume any responsibility regarding
the accuracy of the output information, performance