Voice of the DBA

Tag: T-SQL Tuesday

  • T-SQL Tuesday #184–Mentoring

    This month’s T-SQL Tuesday blog party is hosted by Deborah Melkin, and it’s a good one that asks us where we are making the world better. The topic is mentorship and sponsorship, which is great. We all ought to pay it forward or pay it back, depending on how lucky we are. I believe strongly in this and am glad to see the topic posted.

    I still manage the T-SQL Tuesday list, and I’m always looking for hosts. I have a few scheduled for 2025, but I can use more. If you’re interested in hosting, hit me up at one of these places:

    The Mentoring Experiment

    Andy Warren and I ran The Mentoring Experiment a few times in the past before life got too hard and we decided to pause. I wrote about mentoring as well, and didn’t publish a lot on the experiment as most of the conversations were private.

    I’m somewhat sorry we didn’t continue this, but it proved to be a little overwhelming at that time in our lives.

    My Mentoring Experiences

    I’ve had a few mentors in my life, and while some I’m not comfortable sharing, there are a few that stand out.

    In high school, I had a mentor who was a Navy Pilot. He was a client of my Mom’s and she asked him to give me rides to and from karate, where he was also a student. On our rides, he shared some advice, some thoughts on life, while taking an interest in me. A few things stand out, one of which I still think about today: before I send an email, how would I feel if this were made public? This keeps me from writing too emotionally.

    For the last 20+ years, Andy Warren and I have talked most weeks of the year. We miss a few when we’re on vacation, or I’m traveling, but he has been a great mentor to me, helping me think through life’s challenges. I hope I’ve done the same for him.

    Giving Back

    I think part of what I should do is try and make the world better. Part of that is my trying to get a variety of people to write at SQL Server Central or host here. I’ve reached out through my network to find women or minorities that would participate in our data community, and give them a voice. I’ve been less successful than I would have liked, but I have had a little success.

    I’ve also convinced a few people to speak. I usually look for people at events that are engaged, ask interesting questions, or just have good conversations with me. I’ve found it takes months, but if I encourage them, I’ve gotten some of them to write or speak for free to share their knowledge and grow their own skills. There are a couple successes who many of you likely know their name and a few more that tried it and gave up.

    I think the more you encourage people to engage in life, the more they (and you) get out of it.

    I also try to do this as a coach, getting them to grow and learn more than just the sport. I think it’s worked out well as many kids keep in touch over the years.

    I think many of you can do the same thing. Help others grow and find their own success, with encouragement, support, and a friendly ear.

  • T-SQL Tuesday #183: Improving Permission Management

    This is my (late) answer to my own invitation for T-SQL Tuesday #183. I was very busy a few weeks ago when the invite when out (glad it was scheduled) and I never got this done. This post looks at how I managed permissions in the past.

    Large Enterprises

    Many of us would like to think that large enterprises have standards and they’ve learned about best practices. My experiences in 3 of them were that so often large enterprises were small ones that grew, often with lots of tech debt and busy staff. Even when there are limitations to ensuring good security, often we can’t just fix things because we might break something.

    In one large company (5000+ employees) I found that many of the database servers I managed had permissions set in a variety of manners. Often this included lots of individual permissions granted to logins and users, which was a mess.

    Even when AD groups were in place for departments, they weren’t used as logins in SQL Server since not everyone in a department needed to access the database.

    Cleaning Up

    I hated managing permissions by user. When I started and got a ticket to add a new person to a database for access, I watched someone train me by looking up another similar user in the database, scripting out their permissions, and then search/replace to change the user or login name.

    Not a bad solution, but one that doesn’t scale well over time. What I started to implement was to create an AD group (for Windows users) or a database role (for SQL Users) and then add the login/user to this group or role and assign the permissions from my “other” user to the role. I’d also often move the user to the new role and revoke their specific permissions.

    While this seemed like more work at first, this quickly started to scale well as we had a way to add new users to roles that matched the access they needed.

    Slowly over time we moved a lot of access to AD, which allowed us to remove the burden of disabling users in SQL Server. Plus, we could easily see which access a variety of users had by looking at a role rather than checking multiple accounts. Auditors liked that and it helped us pass various audit checks over time.

    Use roles and groups. They’re not hard and they make things cleaner and easier over time.

  • T-SQL Tuesday #183 Roundup

    I hosted this month’s T-SQL Tuesday party with my invitation asking about tracking permissions. I didn’t get my own post completed in time, but I’ll add it in the next week sometimes.

    In any case, here’s a roundup of the posts I saw. If I missed any, ping me and I’ll add you.

    As usual, Rob Farley is first to respond. Since he’s in AUS and I can’t my New Zealand friends to participate, I’m always glad to see his post appear late on a Monday my time. His post looks at how he does health checks for clients, examining the permissions for logins. He doesn’t care if it’s a SQL login or not, but it should be a controlled login of some sort. He has other thoughts, and it’s a good look at the things you should consider when managing security with an eye on the reality of how people acesss data. Read this one.

    Shane O’Neil has some PowerShell for us. He works with AGs and separate instances, so he needs to ensure the correct logins are on each replic.

    Hugo Kornelis talks about how hard it is to get an overview of permissions. He says there is no permission heaven, but he has some suggestions on how to set up permissions.

    Deb the DBA gives us a few things to think about in a large organization. A nice overview of what to consider and how to talk to others.

    Rob writes about the importance of visibility in managing permissions.

    A relatively quiet month for T-SQL Tuesday (including me). If you are reading this, then I hope you are planning on participating next month. Let us know your thoughts on a topic. If you don’t have a blog, here is how to start.

    If you have a blog, where are you? Share some thoughts? Take 15-20 minutes and help pay it forward by helping others.

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