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.