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 the Foundry website.

To use SQL Census, you’ll need a free Microsoft Azure account – the tool doesn’t consume any Azure resources, it’s just a handy way for us to work more closely with our early users.

How does it work?

SQL Census is a desktop application that inspects your SQL Server and Active Directory and produces a clear overview of each user’s permissions, including what objects are affected and how.

If you’ve ever spent time looking into how SQL Server permissions hierarchies work, I’m sure you’ve come across this ‘helpful’ diagram listing the relationships between the 237 different permissions and 18 fixed roles in SQL Server:

SQL Server 2017 database engine permissions

SQL Census takes the pain out of interpreting SQL Server’s complex permissions hierarchy, allowing you (and your auditors) to gain a clear understanding of your users’ effective permissions.

Choosing the database you want to inspect

After logging into SQL Census, the tool looks for servers on your network and pre-populates a list for you to choose from. Don’t worry if your server isn’t listed, just type it in to the box. You’ll then be given a list of databases on that server to choose from. When you’re ready, hit the Inspect user access button:

SQL Census will then inspect your selected database. 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. Once complete, you’ll see an overview of your users with access to this database.

Capabilities overview

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

User overview

SQL Census surfaces all the associated Active Directory Users associated with a Windows Group SQL Login, allowing you to get more fine-grained information without using multiple tools. You can now dig into individual users to see all permissions they have against all objects in the database:

Permissions map

What permissions a user has is only part of the picture;  you also need to know how they inherit these permissions. On top of this, you will at some point need to explain how these permissions are inherited to your security team or auditor who might not be a SQL Server expert. SQL Census simplifies this by visualising these relationships in a simple Permissions Map:

What next?

Thanks to our Early Access users, SQL Census is evolving rapidly and we’re working hard to make it more flexible and robust. You can help us shape SQL Census by giving us feedback (the more brutally honest, the better) via the in-tool support widget (the red bubble in the bottom right), or by emailing us.

The next major feature on our roadmap is generating a user access permissions report. There are many different forms these reports can take and we’d love to get your input into what report features are the most valuable to you.

SQL Census in-app support widget

Try SQL Census now

Share this post.

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

Related posts

Also in Audit & Compliance

Redgate data governance survey reveals database DevOps is the key to GDPR compliance

A major new data governance survey from Redgate demonstrates there are important GDPR compliance issues that need to be addressed – and that a DevOps approach to database development can provide the...

Also in Blog

The four pillars of DevOps

Firstly, let's get something out of the way. DevOps is something that polarizes people because there are various definitions of what DevOps is. I actually never use the word with technical people beca...

Also about SQL Census

SQL Census update: new server view

SQL Census is the latest prototype to come out of Foundry, Redgate’s research and development division. It helps you trace SQL Server user access permissions. You can use it to for free of charge by...

  • samgreene

    I’m liking the tool, but my co-workers will be concerned with entering their Azure credentials to use this app. Why is this a requirement?

    • Ali

      Hi, I work on the Foundry team. I’m glad you like SQL Census 🙂
      The tool itself doesn’t connect to or consume any Azure resources, it’s just an authorisation step to help us work closely with our early users; we can understand their needs better and find and fix bugs more quickly than if we didn’t have it. Hope that helps.

  • Hans

    I agree with @samgreene:disqus on Azure being a limitation.
    I don’t have Azure currently in my shop, and while I would definitely join in on SQL Census, I don’t have the time or need for getting to know about Azure, let alone getting the authorization to setting it up in our environment. If I could just download and try it out, I would have done by now.

    SQL Census is a great idea, though. I just happened to spend the last week digging up security auditor scripts on the internet to help me getting an overview of the user-specific permission settings on our SQL Server, as we have moved AD domain and all users are “new” to the machines… NOT having the users of the databases on the instance level and in the AD server is really messing up the native reports.

    • Ali

      Hi, I’m an engineer on the Foundry team.
      The Azure credentials you supply are just to help us work closely with our early users. It helps us understand what our users need and allows to find and fix bugs more quickly.

      SQL Census doesn’t use your Azure account to connect to a server and doesn’t consume any Azure resources. You can create a free Microsoft/Azure account here: https://azure.microsoft.com/en-gb/free/ .

      If you’d like to be kept up to about major updates and releases regarding SQL Census, you can register here:
      http://www.red-gate.com/foundry/audit-and-compliance/#signup

      Thanks

  • Organization will not let us use Azure

    • Ali

      My name’s Ali and I’m an engineer on the Foundry team.
      I’m sorry to hear that this is a blocker for you. As the product matures, it’s unlikely that we’ll keep this login step as a requirement.
      You can register your interest with using form below so you’ll be notified about major updates and releases:
      http://www.red-gate.com/foundry/audit-and-compliance/#signup
      Thanks

  • John Bigler

    Very interesting tool. As others have mentioned, the required Azure login is kind of a deal-breaker for any normal usage. Hopefully, this is just a beta testing requirement.

  • Donald Kolenda

    Could the tool possibly provide a better solution for monitoring historical transactions? I would like to replace the complexity involved of setting up SQL Audit on servers, and provide better reporting at the server an database level. I also think the Azure interface is a great way to go.

  • Dirk Klein

    Hi there Ali,

    We would really like to try this new audit tool but the authentication to Azure is not working for some reason. This is for version 0.1.19 Could you please give some guidance or steer me into a direction to be able to give it a try? Keep getting this error no matter which account or permutation of the directory name being used: No service namespace named ‘.onmicrosoft.com’ was found in the data store

    Kind regards