It’s the first T-SQL Tuesday of the year! Arun Sirpal (b/t) is our host this month. Thanks, Arun! The subject he has selected is Your Technical Challenges Conquered. This is a particularly hard subject for me. Generally, when I have a problem and come up with a solution I blog about it right then. That means I don’t really have a backlog of solutions to blog about. In the end, I decided to blog about an on-going solution I’ve been working on. I’ve probably put in greater than a dozen posts on this so I thought I would describe the journey a bit.

The problem

I work for a large company that’s been around a while. Now, if you’ve worked for a company for a few years, you’ll realize that standards change over time. If you’ve worked for a big company, you’ll realize that different groups have different standards. Then add in buying pieces of business with existing servers and you can end up with a big mess. One of the more difficult things to deal with can be security. Some instances use only SQL authentication, some only windows authentication, some use a mix. Some instances use roles, others don’t. The worst is when you get a single instance that has odd mixes of everything.

Another fun aspect of a large company is constant changes. People moving from group to group, divisions merging and separating again etc. This means we get a lot of security requests. Some of our common requests include tracking down an active directory group that has the correct permissions or copying the permissions from one service account to another. Now I love SSMS, it has a lot of great features. Unfortunately, searching through security is not one of them.

The solution

My solution to this type of problem is almost always going to be scripting. So I wrote a series of queries to help me. Now these had some flaws. Mostly though, they were just cumbersome. I cleaned them up some so I could share them but it just wasn’t enough over time. So I converted them to stored procedures (sp_DBPermissions and sp_SrvPermissions). Over time I’ve improved them (v2.0, v3.0, v4.0, v5.0, and finally v6.0). Each time I’ve added bug fixes and new features, including things like a parameter for principal type (active directory user, active directory group, SQL login, etc), the ability to run against a single database or all of them at once, the ability to pull all server level principals associated with a given database, and so on and so on. Most recently I received a number of audit requests for summaries of existing permissions. This lead to me adding a reporting option.

The moral

I’ve blogged plenty about this particular problem and its solution, so here is a bit of value-add for getting this far. You’ll notice that I crafted a solution, but I didn’t stop there. I added to it, cleaned it up, added more, and more, and more. I seriously doubt I’m done (in fact I know I’m not) but over time I’ve developed a solution that goes far beyond what I originally intended. So my suggestion to you? Once you have a solution don’t let it sit at that. Pick at it, improve it over time. You’ll be amazed at what you end up with.