Simplify and Improve Your Security Model With SQL Census

In the R&D division of Redgate, Foundry, we're working on a new tool, SQL Census, in an effort to make your SQL Server permissions more manageable by seeing who has access to your servers and restructuring existing access rights into a simpler and more compliant format.

We're now building out the SQL Census preview to make improving your security model a simple, more efficient and less painful process. We're looking for people who are currently working to improve their SQL Server security to co-create with. If you want to have direct access to the Census team on a weekly basis to help solve your problems, .

Migrating to your desired security model can be a daunting and challenging task. On the one hand, getting the time to analyze and roll out major changes requires relentless clicking about in SQL Server Management Studio and hours of data-munging in Excel, followed by painful meetings to negotiate what access is really needed. On the other hand, following the Scout Rule and making things better as you go would take months if not years to make a difference.

Here's how our plucky DBA, Pam, might go about improving her SQL Server security model:

Just Give 'em the Same Permissions as the Rest of the Team

"Business is going great! Last year was the best year ever, and this year we'll be opening a new sales office in Germany. Stefan has already been hired to head up sales in the region and start hiring his team."

Sally, the global head of sales, has asked for Stefan to be given access to the sales data. Pam, our DBA, cracks open SQL Server Management Studio to take a look:

She starts by looking for an Active Directory login for the Sales team on the database Server... "Woah, there's a ton of individual sales team AD user logins on this server. If there was a Sales AD group, I'd just add Stefan to it and I'd be all set. Oh well, let's take a closer look then."

She then has a look for a [Sales] database role, hoping all the necessary permissions will be granted to it and be following best practice. "Hmm nothing there. How is the Sales team getting access to this Sales data then? Uh oh."

Pam goes back to one of the other Sales users she recognizes to look at their securables. "Yikes, Sam's had all his permissions added discretely! Urgh, even worse, he's also got some permissions on the Warehouse data. Let me take a look at another sales account to see if it's the same."

After a few more clicks, Pam finds herself deeper down the rabbit hole.

"I've now been through four different sales users and they ALL have different permissions. Why hasn't a database role been created for the sales team!? I need to clean up this mess but it's going to be a nightmare working out all the common permissions across tens of logins. Plus, it's only going to get worse as more sales staff are hired for the new office. I've got some time on Friday to sort this out properly, but for now, I'll script out one of the existing sales team's permissions for Stefan."

SQL Census to the Rescue

Friday comes around and Pam has miraculously managed to find a couple of hours to proactively improve her data estate. Pam opens SQL Census to create a new Sales database role by extracting the common permissions from the existing Sales team users.

First, she inspects the database and can see all the discrete permissions that have been granted in the database, sorted by the number of users with those discrete permissions. "Oh, this is cool, I can instantly see where I can make a big dent in these discrete permissions and easily improve our security model."

She starts choosing the discrete permissions for the sales objects. "Great, I can see most of the Sales team, including Stefan, Sam, and Sally, will be added to my new role. It's also only selecting users that have all those permissions in common. Neat!"

Pam's selected all the permissions she wants to turn into a role and clicks Generate scripts. "I wonder what will happen to Sam's Warehouse permissions. Oh, I see, Sam will be added to the new role and only the permissions I selected will be revoked from him — he won't actually notice this change as it doesn't affect his access."

Pam wants to deploy the changes but wants to be sure she can rollback the changes as quickly as possible. She exports the scripts so, if necessary, she can do this later.

Pam deploys the new role. "Great, now I only have to add the new sales hires to a single role. I'm going to see if I can extract another role for those Warehouse permissions Sam had. I bet there are other users with those discrete permissions."

Help Us Help You!

Before we can continue on our journey to help you improve your security model, we need to find out more about how SQL Census can help you. Have you had similar challenges to Pam? Or different ones? What else should be considered? We're looking for people who are currently working to improve their SQL Server security to co-create with.

If you want to have direct access to the Census team on a weekly basis to help solve your problems, download the preview of SQL Census and give us your feedback.