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.