Voice of the DBA

Tag: SQLNewBlogger

The #MS_DatabaseConnector## Role: #SQLNewBlogger
I had seen these roles in various instances, but never dug into the new Fixed Server roles added to SQL Server. They are supposed to help ensure the principle of least privilege is followed.

In this post, I look at one of these, the ##MSDatabaseConnector## role.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Users and Logins

This role is a new one that contains very narrow permissions. It allows a login to connect to any database without requiring a user account mapping. Typically if I create a login for “Steve”, I also create a User in a database that maps here. The convention for most people is to create a user “Steve” that links to the login “Steve”. Typically this is with code like:
```
CREATE LOGIN [Steve]
WITH PASSWORD = N'asdfasdf' MUST_CHANGE,
     DEFAULT_DATABASE = [master],
     CHECK_EXPIRATION = ON,
     CHECK_POLICY = ON;
GO
USE [sandbox];
GO
USE [Westwind];
GO
USE [AdventureWorks];
GO
CREATE USER [Steve] FOR LOGIN [Steve];
```
NOTE: This isn’t the real password.

Note 2: Don’t use passwords like this

NOTE 3: Don’t skip the checks.

Here I am mapping a user to a login, and while the name is the same, these are different objects. If I want the user, Steve, to have access to objects in AdventureWorks, I need to grant rights, or add the user to a Role.

I could also do this:

USE [sandbox] GO CREATE USER [Stephan] FOR LOGIN [Steve] GO
In the Sandbox database, there is no User Steve. Instead, you can see below from SSMS that there is a different user here. My database has no “Steve” in the users, but “Stephan” is mapped to the login Steve.

The Login properties shows this as well.

I added a new user with this fixed server role, and no other database mappings. Here is the server role:

And the user mappings (trust me these are all blank)

I then logged in with this user to the AdventofCode database. As you can see, there are no users here for this login.

The login can connect, however, and I see the current user as the login name.

What can the user do? Well, he can’t access this table, 2021.Day1.

He can access schemas, which I saw with SQL Prompt Intellisense. That makes sense as the sys.schemas dmv is open to the public.

I granted rights to public for one table.

The login (or user with public) can query this.

If I check permissions for the login, I don’t see anything in the database other than basic things: connect, view CEK, view DMK.

This is interesting. It’s like a pseudo-user has been created for this session, mapped to the login. I’m not sure if this is correct or intended, but it’s what I’ve observed.

I don’t know how useful this permission is, though I’m sure someone has a use case where they want something like a monitoring system, or maybe a pen-test available to check what a public user can see in every database.

SQL New Blogger

While I was investigating this role, over the course of testing a few things in 10-15 minutes, I sketched this post. I then spent another 20 minutes capturing some code and screenshots to see how things work.

This is a good post of evaluating how things work and better understanding the system. I could use this as guidance, and perhaps tell an AI whether this role is useful or not. Or to avoid it.

Write your own post, maybe even using AI to help you learn about this role or others. Those skills will help you in the future and you can showcase them for others.
November 4, 2026
Experimenting with CHOOSE: #SQLNewBlogger
I was looking over the CHOOSE() function while editing an article at SQL Server Central. I realized there isn’t a documentation note on how many parameters can be included. I decided to test.

This post looks at the tests and results.

Tl;dr It appears 3925 is the most arguments.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Building a Script

Since I want to know where things fail, I decided to make a short T-SQL script that tests increasing numbers of parameters. This script builds a dynamic CHOOSE() select using a number of parameters and then loops for a set number of times, adding 1 parameter each time.
```
SET NOCOUNT ON;
 
DECLARE @MaxValues int = 5150;   -- largest number of CHOOSE value arguments
DECLARE @n         int = 300;    -- current number of value arguments
DECLARE @i         int;
DECLARE @idx       int;
DECLARE @args      nvarchar(max);
DECLARE @sql       nvarchar(max);
 
WHILE @n <= @MaxValues
BEGIN
    -- Build value list: 'Value 1', 'Value 2', ... 'Value n'
    SET @args = N'';
    SET @i = 1;
    WHILE @i <= @n
    BEGIN
        SET @args += CASE WHEN @i > 1 THEN N', ' ELSE N'' END
                   + N'N''V' + CAST(@i AS nvarchar(10)) + N'''';
        SET @i += 1;
    END;
 
    -- Random index in 1..n (CHOOSE is 1-based; out of range returns NULL)
    SET @idx = ABS(CHECKSUM(NEWID())) % @n + 1;
 
    SET @sql = N'SELECT ' + CAST(@n AS nvarchar(10)) + N' AS ArgCount, '
             + CAST(@idx AS nvarchar(10)) + N' AS PickedIndex, '
             + N'CHOOSE(' + CAST(@idx AS nvarchar(10)) + N', ' + @args + N') AS Result;';
 
    PRINT @sql;                 -- show the generated statement
    EXEC sys.sp_executesql @sql; -- run it
 
    SET @n += 1;
END;
```
This returns the command run. For the first 2 values of n, setting @n to 1 and @max to 2, I get this:
```
SELECT 1 AS ArgCount, 1 AS PickedIndex, CHOOSE(1, N'V1') AS Result;
SELECT 2 AS ArgCount, 1 AS PickedIndex, CHOOSE(1, N'V1', N'V2') AS Result;
```
This let me test different values. I tried various values and eventually got this error:

I tried this a few different times, disconnecting and reconnecting and it seemed that I could get the number of parameters up to 3925. It failed sometimes at 3924 and sometimes at 3925.

I’ve submitted a PR to add the max number to the docs, but it appears CHOOSE() works at a level that would cover lots of items. I see this as a small syntactic sugar add, and if I really needed to index 1000 entries, a join with a reference table would be a better choice.

SQL New Blogger

This script took a few minutes to write, but the testing took about 45 minutes. I was running this in the background with different values. 1..256, 255..1024, etc. I noticed that SSMS responded slower, so I started jumping to small ranges at large values to find the error.

This post took me about 20 minutes to put together after the testing, which included the PR for Microsoft. This is an easy way to show that you can examine possibilities and find the limits of your code. That helps us decide how scalable or robust our solutions are.

You could write this post and impress an interviewer. Choose your own T-SQL function and run an experiment.
October 28, 2026
The SUM of Nothing: #SQLNewBlogger

I caught this interesting item over on Pinal Dave’s blog: Eleven Interview Questions that Look Too Easy. I decided to give you a few thoughts from me on the SUM one.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

The Sum of Nothing

I am guessing more people working with SQL know that if you have a NULL in your data and try to sum the column, you get the NULL ignore. After all, you can’t sum up values if one is unknown. The code and query below show this:

Note, with ANSI_WARNINGS I get a note in the Messages tab:

However, what if there are no rows? Would you expect a 0, because if there isn’t any data, the sum is zero, correct?

No.

Why? The docs don’t mention this (I’ve added a PR). The ANSI standard notes that if all values are NULL or the set is empty, NULL is returned.

Most of us don’t query empty tables, but we could get an empty set. Remember, the column list, and therefore aggregate, is evaluated after the WHERE and JOIN clauses. Therefore, as you see below, I could get a NULL in a sum where I expect data.

Make sure you account for this in your queries.

SQL New Blogger

I read reading another blog (Pinal’s) and realized this was interesting. I thought about if I’d have a problem and realized that I could because I’ve often assumed there is some data, but if I let users filter data from an app, I could return NULL.

Easy to write, about 15 minutes to setup and do. You could do this.

October 5, 2026
Differences Between xp_readerrolog and sp_readerrorlog: #SQLNewBlogger
I was creating a question on sp_readerrorlog and realized that this procedure is different from the one it wraps: xp_readerrorlog. This post digs into a few differences.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

The New System Stored Procedure

For most of my SQL Server career, it’s been a habit to use xp_readerrorlog to query the log. This was code I learned a lot time ago, and it’s worked in every version of SQL Server. Despite being undocumented, as you can see below:

xp_readerrorlog has been written up by a few people at SQL Server Central, including Nagaraj Venkatesan and Ken Fisher, as well as in some forum posts (one, two). However, it’s not well documented. I usually end up Googling for the parameters as I need them.

Until now.

Here are a few differences I’m documenting, so I will hopefully remember them.

Note: I got a few different results from AI, which were at best incomplete, and sometimes wrong.

sp_readerrorlog has different permissions. This proc works with anyone that has VIEW SERVER STATE, which is sysadmin, serveradmin, and security admin. Or with the permission granted.

xp_readerrlog has extra parameters. Both of them have these parameters:
1. error log number (0 based)
2. error log type, 1 – SQL Server, 2 Agent
3. search value – needs to be NVARCHAR
4. second search value, NVARCHAR as well
xp_errorlog adds 5 and 6, and 7.
1. start time (datetime)
2. end time (datetime)
3. sort order (ASC,DESC)
Those are the main differences I see, and if you want to filter or sort, you need the extended stored procedure, not the wrapper.

I also learned to be careful of AI, as some of the data wasn’t correct from Google or Claude, so I need to verify what I get back and test how things work.

SQL New Blogger

I was investigating something and noticed a difference. This post was about 15 minutes to write, along with some testing of the parameters to verify what worked and what didn’t. That testing will make future posts quicker, as I’ll reuse some code.

This is a quick example of showing some knowledge, and including the warnings about AI. You can write this post and give someone confidence you’re a good choice for the next person to manage their database servers.
September 16, 2026