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 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.
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:
- Server Administrator
- Database Administrator
- Database Owner
- Schema Admin
- Data Writer
- Data Reader
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:
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:
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.
Also in Audit & Compliance
SQL Server 2008 and SQL Server 2008 R2 are out of extended support as of July 2019, but the end of bug fixes, security updates and ongoing support has far-reaching data privacy implications, as James ...
Also in Blog
As I’m sure you’ve already heard, Redgate recently celebrated 20 years of making ingeniously simple software, but did you know that we’ve also been supporting the Microsoft Data Platform communi...
Also about 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 rest...