Author: way0utwest

  • 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.

    2026-09_0090

    The Login properties shows this as well.

    2026-09_0091

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

    2026-09_0093

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

    2026-09_0094

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

    2026-09_0095

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

    2026-09_0097

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

    2026-09_0098

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

    2026-09_0099

    I granted rights to public for one table.

    2026-09_0100

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

    2026-09_0112

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

    2026-09_0113

    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.

  • Planning for Change

    Update: My apologies to Randolph for the incorrect pronouns. They should be correct now.

    Over the last year, I’ve been asked numerous times when I’m plan to retire. This question has come from friends, parents of kids I coach, coworkers at Redgate, and even from Microsoft. A few people have asked about my succession plans for leaving, as I have a public site at SQL Server Central, and the charity in SQL Saturday. I’d like those things to continue past my time, so I am thinking about how to do that. I already have plans to hand off T-SQL Tuesday next year to someone else.

    No worries, I’m not retiring soon. I have quite a few years left, though a decade is probably the outer range for me.

    Randolph West retired from Microsoft, and perhaps from tech. We’ll see, and I hope Randolph’s writing efforts go so well that they make that a new career. I love reading, and we always need great writers. I wish Randolph the best and hope their endeavors are successful. However, I’m going to miss Randolph, as they have made the SQL Server docs so much better, and we’ve corresponded about my PRs many times over the years. I have a hit-and-miss acceptance rate, and often, Randolph made my edits better.

    In any case, making a big change in your life is hard. Whether this is career or personal, I’ve seen many people struggle with big changes. I’ve seen many, many more people be afraid to make a big change, often wishing they could but continuing along their current path anyway. That continuation often involves a lot of stress and many complaints, which is why I constantly advise people to make a plan, build their skills, and then make a change.

    Whether you’re planning for retirement, a new job, or even a vacation, it pays to ensure your world is set for someone else to take over. Even if you don’t leave, having things organized, scheduled, planned, maintained, etc. makes your job easier every day. This doesn’t mean everything is automated, though automations are helpful. Routines that are documented, protocols that catch and handle exceptions (automated or not), clean boundaries between tasks, even clear communication- these are the things that ensure that your job runs smoothly. Often we create these habits that we internalize and (mostly) consistently execute, but we could be better and smoother with a little effort. Especially with AI agents (helping and watching, with approval, not doing all the work independently).

    I saw someone say recently that it’s not just death and taxes that are inevitable. Change is also something you can count on. I think working with the idea of change helps us prepare for it, whether it comes sooner or later.

    I’m working on setting up continuity for the things I want to live past me, which are both inside of work and outside of it. Especially things for my family, where I want to document how things around the ranch work in case they need to take over.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • Great Things

    “Great things are done by a series of small things brought together.” – Vincent van Gogh, Dutch Post-Impressionist Painter.

    I ran across this quote recently and it struck me that it’s expresses a thought I’ve had for some time. Instant, magical success isn’t often what it appears. Anyone that seems to have sprung up overnight with success in anything has often spent a lot of time getting ready.

    Outside of lottery winners, of course. If you bought a ticket, that’s instant success. Unless you buy a dozen every week, then you’ve likely been doing your own (poor) preparation over time.

    Whether it’s software, sports, business, anything that takes off, goes viral, or suddenly catches the public’s collective eye is the result of a lot of small things. Practice, learning, continuous improvement. It’s a formula that works in music, in sports, in manufacturing, and in software.

    Perhaps the most important part of this quote is the ending. Things have to be brought together. In software, we work in teams, and we have to coordinate and collaborate. Whether with other human developers, AI agents, or Operations. We have to learn to ensure our efforts, our knowledge, our evolution works together. This might be especially important in ensuring database and application code changes mesh well. That’s becoming a concern for more organizations, and more management that realizes that the database has to become a part of the software development life cycle. Either developers need to learn more, or work closer with database staff.

    We can do great things, we can build amazing systems, but it takes effort, and it takes teamwork.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • Cleaning Up Azure MS Defender Billing

    I have a few Azure subscriptions, which are fairly small and not used that often. Mainly I do some SQL Server or Azure SQL experimentation. Sometimes I might create another resource, but I try to not waste resources and disable them quickly (or drop them) when I’m done.

    However.

    I hadn’t done much in the last couple of months on Azure. For some reason, I decided to check my bill. What I found was that there were charges for $35-ish/month. This is within my free credit limit as an MVP, but that seemed high. I have a few serverless dbs and some storage, but I was expecting more of a $15 charge.

    I decided to investigate. I started with the Billing section, where I saw that Microsoft Defender was swamping the charges in my bill. I don’t remember enabling this, and typically skip this stuff since I work with test data and nothing sensitive.

    2026-10_0159

    As you can see, the Defender items are charged, but not linked to a resource?

    WTF? How do I debug this? I have a small subscription, maybe a few dozen resources. Imagine you had a larger estate? I decided to ask Copilot. I get this result:

    2026-10_0146

    OK, environment settings, let’s check the items under here.

    2026-10_0147

    Now, where is this enabled? I checked the Defender properties for the subscription.

    2026-10_0148

    I went into the various Azure SQL databases, clicked the Security section for Defender and I keep seeing nothing enabled.

    2026-10_0160

    I’m ready to open a case, and waste the days and then multiple contacts trying to work through this with some customer service person in another time zone, who reads docs.

    Digging further, I keep clicking around, Copilot being of no use here. I eventually start clicking in every resource, and I get to the logical server for my Azure SQL Databases. Here’s where I see it, at the server-level.

    2026-10_0154

    I don’t think I turned this on, but perhaps I tested something.

    In any case, I click settings and then disable Defender.

    2026-10_0155

    I’m less concerned that this got turned on, because I might have done that. I would love some auditing of actions to determine this, as I could see someone else in my team (if my team were bigger than me and the 3 dogs) trying this out. I’d want to know who to talk to about changing settings.

    More, I’m annoyed that the bill doesn’t link to resource settings, or that this being enabled at the logical server level isn’t reflected and noted at the database level.

    This was a 45 minute investigation that should have taken 3, so a word of warning to you Azure users. Be careful what you enable, and when you have billing issues, check the resource group and logical servers, in addition to your databases.