The Bit Bucket

SQL: Determining the Windows Groups for a SQL Server Login

I saw a question on a SQL Server mailing list about how to determine the Windows groups for a given SQL Server login.

That’s actually easy for a sysadmin login, as they have IMPERSONATE permission for other logins/users.

Here is an example procedure:

USE tempdb;
GO

CREATE OR ALTER PROCEDURE dbo.GetGroupsForALogin
@LoginName sysname
AS
BEGIN
    EXECUTE AS Login = @LoginName;

    SELECT [name] AS GroupName,
           usage AS Usage
    FROM sys.login_token 
    WHERE [type] = N'WINDOWS GROUP';
END;
GO

When I executed this on one of my SQL Server 2025 systems this way:

2026-06-03

Fabric RTI 101: Handling Late-Arriving or Out-of-Order Data

In an ideal world, every event in a data stream would arrive exactly when it was generated and in perfect chronological order. But in reality, streams are messy. Events can arrive late, out of sequence, or even replayed after retries. This is a normal characteristic of distributed systems — not a failure — and it’s something we need to design for in any real-time analytics pipeline.

There are several reasons why events might not arrive in order. Sometimes there’s network latency between devices and the ingestion point. Other times, devices might buffer data locally and send it in batches when a connection becomes available. In cloud or IoT scenarios, retries or transient service interruptions can also cause duplicates or delayed messages. So, when you’re analyzing a stream, the order of arrival isn’t always the same as the order of occurrence.

2026-06-02

I recently received a review copy of Extreme DAX: Take your Power BI and Fabric analytics skills to the next level by Michiel Rozema, Madzy Stikkelorum, and Henk Vlootman from my friends at PackT.

Authors

Michiel Rozema is a long-term IT industry veteran. He was the data insight lead at Microsoft Netherlands and launched Power BI in the country. Michiel works as a data analyst and architect for his company Qanto. He is a fellow Data Platform MVP.

2026-06-01

I’ve mentioned that in Microsoft Fabric, both KQL and SQL are available as query options — and they each have their strengths. So when should we use each?

KQL, or Kusto Query Language, is purpose-built for analyzing logs, telemetry, time-series, and streaming data. It’s optimized for massive volumes of events, and it’s designed to help you spot trends, anomalies, and behaviors over time. If you’re working with sensor readings, application traces, or event streams, KQL is the natural fit.

2026-05-31

General: Entering a backslash when using UK keyboard layout on US keyboard

This is more for my own reference than anything else but I needed to enter a backslash key while using a US keyboard but with UK keyboard settings.

After trying pretty much every key on the keyboard in all combinations, I realised there was no key combination that would do this directly. Curiously it’s a common problem and I found untold blog and forum entries that were not helpful. They basically said to change your input to US when using a US keyboard.

2026-05-30

KQL, or Kusto Query Language, is the primary way we interact with data stored in a KQL database. It’s a read-only, declarative language — meaning you describe what result you want, rather than giving the system a sequence of procedural steps to perform. It’s designed specifically for interactive analytics on large, fast-moving datasets such as telemetry, logs, or IoT events, where you need quick insights rather than data updates.

One of the biggest differences you’ll notice compared with SQL is the pipeline syntax. Instead of writing long, nested queries with sub-selects or CTEs, KQL uses the pipe operator (|) to pass the output of one operation directly into the next. This creates a kind of query “flow” — very readable and very modular. For example, you might start with a table name, filter the rows, then summarize, and finally sort the results, each step connected by a pipe. It reads much more like a logical sequence of transformations than a single dense statement.

2026-05-29

SQL: Reliably Dropping a Database in a T-SQL Script is Too Hard

I often need to create a T-SQL script that reliably drops and recreates a database each time it is executed. A common requirement is that I’m building a script to test come concept and I want to just re-run a script to get back to a known point after cleaning up all that I’ve done before. I could even just want to create a database in a particular way, and I want to make sure it doesn’t already exist.

2026-05-28

AI: Data Science Summit AI Edition 2026 in June - Session 2

I recently mentioned that the AI Edition of the Data Science Summit is coming in June and that this time, I’m presenting two sessions.

The second session is Calling Ollama Based Text Embeddings Models from SQL Server 2025, and I’ll be discussing how the AI features that have been added to SQL Server in 2025 can be used with text embeddings coming from Ollama-based text embeddings models running locally on your servers.

2026-05-27