Voice of the DBA

Tag: T-SQL Tuesday

  • T-SQL Tuesday #185–Being a Video Star

    tsqltuesdayThis month we have an interesting invite. Erik Darling is the host, and since he does a lot of video blogs, he’s asking for a video submission for T-SQL Tuesday. The topic and content is up to each of us, but the format is video.

    That’s funny as I’m writing this blog, but the content below is in the video. What’s in there? You have to watch.

    I’m also looking for hosts for the future and have a few slots in 2025 still available for H2. I’ll also note that any month could be a video post for the topic if you want, though I suspect a few hosts might not love having to watch videos. Some, however, will love it.

    My Submission – Testing the Flat File Wizard

    Here’s my submission:

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