ronmichael’s gists

ronmichael / query-adsi.sql

Last active November 14, 2019 09:36

Query ADSI from MSSQL.

	-- query all groups
	SELECT * FROM OPENQUERY( ADSI, 'SELECT objectGuid, distinguishedName, name FROM ''LDAP://dc=mydomain,dc=local'' where objectClass = ''Group'' ')

	-- query all users
	SELECT * FROM OPENQUERY( ADSI, 'SELECT objectGuid, distinguishedName, samAccountName, name FROM ''LDAP://dc=mydomain,dc=local'' where objectClass = ''User'' ') where samAccountName not like '%$'

	-- to query ALL group memberships, you'll have to cursor through
	-- all groups and get their members with a statement like this:
	select * from OPENQUERY( ADSI, 'SELECT objectGuid, name FROM ''LDAP://dc=mydomain,dc=local'' where objectClass = ''user'' and memberof=''CN=[group name],OU=[org unit],DC=mydomain,DC=local'' ')

ronmichael / concat-with-xml.sql

Last active December 11, 2015 09:49

Use XML to concatenate multiple records into a single field in MSSQL

	select
	s.name,
	stuff (
	( SELECT ',' + c.name AS [text()] FROM cities c where c.stateid=s.id
	FOR XML PATH('') ),
	1, 1, '') cities
	from states s;

	select
	s.name,

ronmichael / big-tables.sql

Created January 21, 2013 00:01

Analyze all your tables and identify the big ones with this MSSQL script. Originally created Bill Graziano ([email protected]) of SQLTeam.com.


	declare @id int
	declare @type character(2)
	declare @pages int
	declare @dbname sysname
	declare @dbsize dec(15,0)
	declare @bytesperpage dec(15,0)
	declare @pagesperMB dec(15,0)

	create table #spt_space

ronmichael / analyze-queries.sql

Created January 21, 2013 00:01

Analyze your top MSSQL queries with this script

	select top 100
	qs.max_elapsed_time/1000000.00,
	db.name db,
	ob.name object,
	substring(st.text,1,100) preview,
	total_worker_time/execution_count AS [Avg CPU Time],
	SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
	((CASE qs.statement_end_offset
	WHEN -1 THEN DATALENGTH(st.text)
	ELSE qs.statement_end_offset

ronmichael / fragmented-indexes.sql

Last active December 11, 2015 09:49

Analyze index fragmentation in your MSSQL database

	SELECT
	DB_NAME(DPS.DATABASE_ID) AS [DatabaseName]
	,OBJECT_NAME(DPS.OBJECT_ID) AS TableName
	,SI.NAME AS IndexName
	,DPS.INDEX_TYPE_DESC AS IndexType
	,DPS.AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation
	,DPS.PAGE_COUNT AS PageCounts
	FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) DPS --N'LIMITED') DPS
	INNER JOIN sysindexes SI
	ON DPS.OBJECT_ID = SI.ID

ronmichael / add-cert.sql

Created January 21, 2013 00:15

Add a certificate to a stored procedure in MSSQL

	-- in this case we're giving the cert permission to alter the schema
	-- (maybe explicitly disably identify and insert something into an identity column)

	-- set up cert with permission to alter schema...
	CREATE MASTER KEY ENCRYPTION BY password = 'opensesame';
	CREATE CERTIFICATE SchemaCert WITH SUBJECT = 'Schema user Certificate'
	CREATE USER SchemaCertUser FROM CERTIFICATE SchemaCert
	GRANT ALTER ANY SCHEMA TO SchemaCertUser

	-- run or rerun the following line whenever you alter procedure,

ronmichael / ssrs-logs.sql

Created January 21, 2013 00:16

Analyze SQL Server Reporting Services (SSRS) report usage. Note that the log tables do get cleared automatically after 30-60 days or so, so you need to run this regularly.

	SELECT
	ex.UserName, ex.Format,
	cat.Path,
	cat.Name, ex.Parameters,
	CONVERT(nvarchar(10), ex.TimeStart, 101) AS rundate,
	ex.TimeStart, ex.TimeEnd, ex.TimeDataRetrieval, ex.TimeProcessing, ex.TimeRendering, ex.Status,
	ex.ByteCount, ex.[RowCount]
	FROM ReportServer..ExecutionLog AS ex INNER JOIN
	ReportServer..Catalog AS cat ON ex.ReportID = cat.ItemID
	ORDER BY ex.TimeStart DESC

ronmichael / objectToTurtle.cs

Last active December 15, 2015 22:19

Convert a dotNet C# object into RDF (Turtle)

	using System;
	using System.Collections.Generic;
	using System.Linq;
	using System.Text;
	using System.IO;

	// just some rough playing around converting an object to RDF Turtle.

	class ObjectToTurtle
	{

ronmichael / fixEventReceivers.ps1

Created April 10, 2013 21:10

After migrating from SharePoint 2010 to 2013 we found that some workflows ran multiple times. The cause was multiple event receivers left over on lists from SharePoint 2010. So we created this script to go through all the lists on all the sites on your server and remove the old SharePoint 2010 event receivers. Credits to http://sharelockpoint.wo…

	# delete old SharePoint 2010 event receivers to eliminate duplicate workflow actions

	function examineWeb($web) {

	$spLists = $web.Lists;

	for ($listnum=0; $listnum -lt $spLists.Count; $listnum+=1)
	{
	$($web.Title + " - " + $spList.Title)

ronmichael / script-users.sql

Last active December 18, 2015 04:09

Script out all your MSSQL users - with passwords and group memberships - so you can recreate them on another server. Credits to http://weblogs.sqlteam.com/billg/archive/2010/07/08/Scripting-out-SQL-Server-Logins.aspx for the original version; this variation does not require you to create a new function (fn_hexadecimal); it uses the fn_varbintohe…

	select
	'IF NOT EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''')
	CREATE LOGIN [' + [name] + ']
	WITH PASSWORD=' + master.sys.fn_varbintohexstr(password_hash) + ' HASHED,
	SID = ' + master.sys.fn_varbintohexstr([sid]) + ',
	DEFAULT_LANGUAGE=[us_english],
	CHECK_EXPIRATION=' + CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_POLICY=OFF
	GO
	IF EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''')
	ALTER LOGIN [' + [name] + ']

Ron Michael Zettlemoyer ronmichael