2 June 2017
2 June 2017

Simplifying the user access audit with SQL Census

SQL Census is the latest piece of technology to be developed within Redgate Foundry. Still early in its development, it’s a product that’s designed to help you explain to an auditor which of your colleagues are able to access business-critical SQL Server databases. As well as reporting on who can access these databases, it explains what they can do (ie, the capability their permissions give them) and how (ie, the ways in which they’ve inherited these capabilities).

In this blog post we’ll outline a couple of the most common scenarios we heard in our early research, and describe how SQL Census can be used to prepare for a smooth audit and easily provide your auditor with the information they need.

Getting an overview in preparation for an audit

We all work to best practices, but it’s not easy to get a good top down view of who can do what in your estate. We’ve designed SQL Census to give you that view at a click of a button. Let’s explore this scenario.

DBA Debs knows her audit is coming up and it’s going to cover the Accounts database. SQL Census is a SaaS hosted solution that covers your on-prem and Azure data estate and DBA Debs signs up on the site. She downloads a small gateway application that allows SQL Census to inspect her estate.

SQL Census - Start Screen

SQL Census Gateway Download

Debs connects to the Accounts database on her SQL Server and is shown an overview of all server logins and their effective permissions in Accounts. SQL Census knows all the ways that a server login can have permissions allocated: whether through server/database roles, object permissions, ownership and even less well known routes such as CONTROL permissions.

Debs notices that one of the Application Developers, Ali Daw, has DDL Admin privileges in production. She clicks View detail to find out what else Ali can do in Accounts.

User Permissions Overview

It looks like Ali also has rights to read and write data in Accounts too. Debs wants to understand how Ali ends up with these effective permissions so she clicks More detail to find out.

SQL Census inspects Ali’s permissions, not only looking through nested roles in the database and server, but walking through Active Directory to see which groups have permissions against Accounts that he is a member of. It also checks the Accounts server host to see which local machine groups he’s in.

SQL Census User Permissions Map

That’s interesting:  not only has Ali been added explicitly to the db_ddladmin group, he’s also in the AccountsAdmin role which is a member of db_ddladmin.

Now let’s look at these Read/Write permissions – it doesn’t look like Ali’s login has been granted anything specifically on the server. So what’s going on?

SQL Census had a look at Active Directory and found Ali in the AccountsTeam AD group – the accounts team obviously need Read/Write permissions to run the accounting app. This AD group is a login on the Accounts database and has been added to the AccountsUser role which has been granted SELECT, INSERT, UPDATE and DELETE.

Now it’s much easier for DBA Debs to see what’s going on, and take action if necessary.

Providing audit evidence

So Debs now knows the Accounts user access is up to scratch and it’s audit week. Last year, pulling all the user access evidence together for the auditor took hours. She ran custom PowerShell, SQL scripts and used spreadsheets to pull it all together, only to find that the auditor wasn’t happy with a bespoke report and still needed to sit with her to produce a bunch of screenshots in SQL Management Studio and Active Directory.

Our aim is that SQL Census becomes the standard-bearer for SQL Server audit reports. That’s why we’re working with auditors and IT audit consultants to make sure SQL Census creates reports that are easy for your auditor to follow, provides all the user access evidence needed for the audit, and is simple to export.

How? SQL Census understands how SQL Server’s 237 different permissions, 9 fixed server roles and 9 fixed database roles hang together and condenses these into 7 Capabilities:

  1. Server Administrator
  2. Database Administrator
  3. Database Owner
  4. Schema Admin
  5. Data Writer
  6. Data Reader
  7. Connect/Public

This information is collected in a report that’s easy to generate, simple to export and trusted.

What next?

We’ve got an Early Access Programme running. If you’re coming up to an audit, and can commit an hour per week guiding the development of SQL Census, then drop us an email – we’d love to work with you.

In the meantime, you can stay up to date with progress on SQL Census by signing up to hear more over at Redgate Foundry.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

Related posts

Also in Blog

Down Tools Week 2017

Once a year, we hold Down Tools Week at Redgate. It’s our version of a hack week – a chance to put down the day job and spend a week working on something completely different. It’s an opportuni...

Also in Redgate products

SQL Monitor plans for the second half of 2017

SQL Monitor's dev team has made huge improvements to the product over the last year. In the first half of 2017 alone, they released reporting capabilities, support for collecting metrics from VMWare h...

Also in Software development

How might classification and better documentation improve data safety?

In this post, we imagine how auto-classification of data can be used to build better documentation that helps you trust that your organization can use data without posing a risk or compromising regu...

Also about Foundry

Data classification: understanding and protecting your data

A data discovery and classification research project from Foundry

In Foundry, we’re responsible for developing new products and technology to support the changing needs of our customers. We’ve ...

Also about SQL Census

Using SQL Census to audit SQL Server permissions

We’ve just released SQL Census, a prototype tool from Redgate’s Foundry division that makes auditing SQL Server user access permissions much easier. You can try it for free by downloading it from ...