Voice of the DBA

Tag: T-SQL Tuesday

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

  • T-SQL Tuesday #182: Personal Integrity

    It’s time for the first T-SQL Tuesday blog of 2025, with an invite from the first non-founder to host a party, Rob Farley. I reached out to Rob and he graciously agreed to host. His invitation this month is on integrity, leaning towards data integrity.

    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:

    While data integrity is important, I think personal integrity really matters as well.

    Integrity at Work

    I used to work for a large corporation, which I won’t name here. This was a 10,000+ person organization and I helped manage part of the data group for the company. I had a number of reports, 10 or 11, that handled different aspects of production operations across a variety of database platforms.

    It was a busy job, and our environment was far from stable. As is the case, things get cobbled together, become popular, and then limp along for years. This happens in small companies and large ones, but in large ones, I think it continues because when there are problems, we can throw people at the problem. We could throw resources and fix chronic issues, but that wasn’t the case at this company.

    At the time I worked at this company, we had a stacked ranking system for reviews.  Microsoft used to do this (and stopped), but think about ranking everyone in your team from 1-5, 1 being underperforming and 5 being outstanding. The “stacked” part comes from the need to have a certain number of 2s, 3s, and 4s. 1s and 5s were rare (5s more rare). Essentially a 1 meant you were on a performance plan and on the way to termination.

    I had to rank my staff, who worked hard and kept our systems running, despite lots of incidents. We performed some large migrations and upgrades of systems where my staff worked multiple weekends, with no comp time, just meals covered.

    My first integrity stand was that even though I wasn’t officially allowed to give comp time, I did, working through my staff with extra days off that were unrecorded anywhere. This might be more of a violation, and if my boss’s boss had caught wind of this, I would have been terminated. However,  I felt my staff deserved something, as a few of these long weekends weren’t adding business value; they were things executives wanted to do for optics.

    Near the end of the year, we had to rank our workers for annual reviews. I had most of my staff at 4s, with a couple 3s. I put in one person as a 5 for some great work they did. Justifying a 5 is hard and I thought I had a good case.

    In a meeting of our IT department, the VP of Operations told us that we had too many 4s and that each director (1 level above me) would get an allocation of rankings and that each manager had to work to fit their staff inside that allocation. Anyone ranked a 1 would be outside the allocation.

    My second integrity stand was to argue with my boss on why my staff deserved their rankings. According to their job descriptions and performance, I’d ranked then well, even though his quota meant he wanted me to move a couple 4s to 3s and 1-2 people to a 2 ranking. I refused, arguing with other managers whose staff I had seen underperform through the year. I told a few others they needed to absorb the 2s and 3s and not the data team.

    I lost that battle and had some uncomfortable reviews (and the accompanying bonus/salary numbers), but I acted like an adult and told people I had ranked them a level higher and upper management lowered their ranking, not because of performance, but because of a quota.

    As we went through the next year, management decided they needed to cut costs. They had managers compile a lot of numbers that were sent to outsourcing companies in an RFP to essentially remove IT from the company’s books. In a meeting, our CTO tried to spin this as good for workers as the winning company would hire our IT staff. The framing was that this outsourcing would save the company money.

    My third integrity stand was when I questioned the way this worked in a large meeting of IT management and eventually got the CTO to admit that:

    • a) everyone would have to re-apply for their job
    • b) they might not get the same salary (higher or lower, you decide which is more likely)
    • c) not everyone would be hired

    This might work out for some people, but likely those who got jobs at a new company were risking their salary and workload in a new situation. I questioned this as being good for the company overall as we would likely lose lots of knowledge.

    I left shortly thereafter, voluntarily, but I’m sure I would have been let go at a layoff that occurred near the end of that year.

    I hated that job in many ways and was glad to eventually leave. I decided I would never work for a stack ranking company again.