T-SQL Tuesday #183 Invite: Tracking Permissions

It’s time for T-SQL Tuesday again and this month I’m hosting. I realized that I didn’t host in 2024 and since I run the thing, I ought to be a part of it one of these days.

So, this is that month. The format is the invite comes out today and you have a week to write a post. Please post it on your blog and link back to this post or leave a comment on this post with the link to yours. That way I can summarize in a few weeks.

With that, the invite…

Managing Database Permissions

One of the things I see lots of people struggle with is the database permissions for their logins/users. DBAs or Ops people are always getting requests to add people, rarely requests to remove people, and not often enough, requests to audit who has permissions.

Lots of systems that live for years have people with too many permissions but no one knows this.

It could be SQL Server, Oracle, CosmosDB, RDS, PlanetScale, etc. Well not that last one, they haven’t been around for years, have they? They have, since 2018, so maybe people do have this issue in MySQL.

Ultimately, we have to audit or understand permissions at some point. Or we need to update permissions. This could be a developer task (as they add/change objects) or it could be strictly Operations. I’ve seen both.

This month, I’m wondering how you track permissions, which I assume involves some code. Show us a good way to do any of these:

  • check permissions
  • update permissions
  • add new logins/users across systems
  • remove people
  • report on permissions at various levels (user, object, database, etc.)

You should publish your post on Feb 11, 2024 UTC. Link back to this post with a trackback/pingback or a comment.

Looking forward to what you come up with.

Posted in Blog | Tagged , | 7 Comments

Declaring a Complex PK in a CREATE TABLE: #SQLNewBlogger

Recently I was talking with someone who had not named any of the primary keys (PKs) in their database. They used system generated names and when they ran comparisons, they got all sorts of drops and creates they didn’t expect. This post shows how easy it is to declare PKs with names, even with complexity.

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

The Scenario

I’m going to use a schema that I have for baseball data. It illustrates the point well, I think.

For my friend, imagine a table like this one:

CREATE TABLE [dbo].[salaries](
     [yearID] [int] NOT NULL,
     [teamID] [varchar](3) NOT NULL,
     [lgID] [varchar](2) NOT NULL,
     [playerID] [varchar](9) NOT NULL,
     [salary] [int] NULL,
PRIMARY KEY CLUSTERED 
(
     [yearID] ASC,
     [teamID] ASC,
     [lgID] ASC,
     [playerID] ASC
)
)

If I create this table, the system will decide what the PK is. In fact, after I run this, I can see in sys.objects that the name is some random code.

2025-01_0258

What’s worse is that if this were an FK and I were dropping these in some script, I’d have issues in other systems. This name is different on each system.

The better solution is to make a simple change. Before the Primary Key keyword, I can add Constraint and a name, like this:

CREATE TABLE [dbo].[salaries](
     [yearID] [int] NOT NULL,
     [teamID] [varchar](3) NOT NULL,
     [lgID] [varchar](2) NOT NULL,
     [playerID] [varchar](9) NOT NULL,
     [salary] [int] NULL,
CONSTRAINT salariesPK PRIMARY KEY CLUSTERED 
(
     [yearID] ASC,
     [teamID] ASC,
     [lgID] ASC,
     [playerID] ASC
)
)

If I run this, then I have a better named PK.

2025-01_0259

Be explicit in your work. It makes for better code and easier, repeatable, reliable deployments.

SQL New Blogger

This is a simple thing, a code smell, but one that wastes DBA and developer time. This post shows a simple thing you can do to have better code. This took me about 10 minutes and you could do something similar.

Write this and maybe someone asks you how to do this in an interview.

Posted in Blog | Tagged , , | Comments Off on Declaring a Complex PK in a CREATE TABLE: #SQLNewBlogger

Words vs Data

I would guess that most of you reading this are very comfortable looking at data for insights and answers. You might even prefer to provide a result set instead of a picture or chart to a user when they are asking for help with data analysis. However, do you add any words to your analysis to help? Any descriptions, summaries, or conclusions that could be drawn from the data or the picture?

I ran across a blog asking about the right ratio of words to data. The post uses the childhood story of Goldilocks and the Three Bears. Many of you might know the story and have drawn your own conclusions of what the story shows or means. If you read this post, you will find a very different interpretation. While some of you may not think that’s a valid interpretation, it’s possible that some thought that when they first heard the story.

The point of the post is that we can provide data and pictures, but others might interpret things differently. Each of us has our own point of view, our own experiences, and our mood. That last one might lead us to focus on a piece of data or a part of the picture that the author didn’t intend for us to focus on, or didn’t think was relevant. Without any sort of guidance on the narration from the author, we don’t know how closely our interpretation matches theirs.

Many of us have certainly seen others spin data, especially aggregates and statistics, to suit a narrative. However, the idea of providing some narrative isn’t to hide or mislead, but rather give context to what you see in the report. As the blog notes, don’t leave their interpretation to chance. Give them a “well-crafted, objectively reasonable narrative that is supported by your data.”

Or, if you don’t have one, let them know that and ask them to send you one back showing what they see or what they expect.

Steve Jones

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

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

Posted in Editorial | Tagged | Comments Off on Words vs Data

AIs Struggle with Family

This post looks at a logic problem asked to an AI, and which is struggles at finding an answer. This is based on an article noting this question stumps most AIs. The article is at: https://futurism.com/logic-question-stumps-ai

This is part of a series of experiments with AI systems.

The Setup

I loaded a Deepseek model to compare with my Mistral model. I did this with the following commands:

docker run ollama
docker run -d -v ollama:/root/.ollama -p 11434:11434 --name ollama ollama/ollama
docker exec -it ollama ollama pull deepseek-r1:1.5b
docker exec -d ollama ollama run deepseek-r1:1.5b
docker run -d -p 3000:8080 --add-host=host.docker.internal:host-gateway -v ollama-webui:/app/backend/data --name ollama-webui --restart always ghcr.io/ollama-webui/ollama-webui:main

From there, I had a website.

The Logic Problem

In the article, we ask a question.  I asked: >Alice has [5] brothers and she also has [3] sisters. How many sisters does Alice’s brother have?

The response was interesting, using the Deepseek-r1:1.5b model. Here it is, with the entire response in Italics.

First, determine the total number of children in the family by adding Alice’s brothers (5) to her sisters (3). This gives a total of 8 children.

Next, since Alice is one of those 8 children and she has 5 brothers, subtracting Alice from the total gives the number of brothers as 4. However, this calculation leads to a discrepancy because it doesn’t align with the original information provided.

Recognizing that there might be an error in the initial assumption, consider that Alice has 3 sisters, meaning there are at least 4 girls (including herself). Adding her 5 brothers results in 9 children, which still does not match the given numbers.

Finally, re-evaluating the problem, it’s clear that without additional context or information, determining the exact number of sisters a brother has is not possible with the provided details.

Solution:

Let’s break down the problem step by step.

  1. Total Number of Children in the Family:

    Alice has:

    • 5 brothers

    • 3 sisters

    Since Alice herself is one child, the total number of children is:

    [
    \text{Total children} = \text{Number of brothers} + \text{Number of sisters} + 1 = 5 + 3 + 1 = 9
    ]

  2. Number of Brothers:

    Alice has 5 brothers in her family.

  3. Number of Sisters Alice’s Brother Has:

    Since the total number of children is 9 and there are 5 brothers, we can determine the number of sisters each brother has by subtracting the number of brothers from the total number of children:

    [
    \text{Sisters per brother} = \frac{\text{Total children}}{\text{Number of brothers}} = \frac{9}{5}
    ]

    However, this division does not result in a whole number, which suggests that the problem might be interpreted differently.

  4. Alternative Interpretation:

    Considering that Alice has 3 sisters, it implies there are at least:

    • 1 (Alice) + 3 = 4 girls

    Including her own siblings, if each brother has an equal number of sisters, we can find the exact number by dividing the total number of children among all siblings:

    [
    \text{Sisters per brother} = \frac{\text{Total children}}{\text{Number of brothers} + 1} = \frac{9}{5 + 1} = \frac{9}{6} = 1.5
    ]

    This result still doesn’t make sense in the context of having whole sisters.

  5. Conclusion:

    Given the information provided, it’s not possible to determine an exact number of sisters that each brother has because:

    • The total number of children is fixed at 9.

    • Each brother has a different number of sisters based on their position in the family tree.

    Therefore, with the current details, there isn’t enough information to definitively state how many sisters Alice’s brother has.

\boxed{\text{Cannot determine}}

That’s interesting, but is this a Deepseek problem? Let’s try another.

Mistral

Mistral is the first model I played with locally. Let’s ask it the question. This response was shorter, so I’ll add it as an image:

2025-01_0090

It seems the AIs don’t quite understand family, and they’re making simple mistakes in putting things together. I could see humans making this mistake, but they’d catch it quickly if someone pointed something out.

Let’s try one more thing.

What’s a Family?

I then ran this prompt: In a family, if I say Alice has 2 brothers and 1 sister, how many children are in the family?

Easy, isn’t it? If I have 3 siblings, whether brothers or sisters, then there are 4 kids. How does the AI do?

2025-01_0094

I suppose Alice could be male (Alice Cooper), but even in that case, the AI seems to struggle to separate out Alice from the counts. For some reason, even listing the siblings out it counts 5 rather than 4. I guess 2+2=5 (for extremely confused AIs).

Conclusion

This isn’t to imply the AIs aren’t useful or helpful, but rather they aren’t intelligent in a human sense, or even logical in a human sense. They are very well trained search engines that can put things together in a way that we can’t easily program. They can predict the way to approach problems with mimicry of how humans think.

However, they don’t have common sense. They are just working with patterns that are very complex, but aren’t intelligent.

They are useful and can help you solve problems, however, they do make mistakes. Knowing something about the problem you’re asking them to solve is important. These are tools to help us, not tools that replace us.

Posted in Blog | Tagged , , | 2 Comments