DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Automating Data Pipelines: Generating PySpark and SQL Jobs With LLMs in Cloudera
  • Doris: Unifying SQL Dialects for a Seamless Data Query Ecosystem
  • SQL Server Index Optimization Strategies: Best Practices with Ola Hallengren’s Scripts
  • How to Restore a Transaction Log Backup in SQL Server

Trending

  • Comprehensive Guide to Property-Based Testing in Go: Principles and Implementation
  • Unlocking AI Coding Assistants Part 2: Generating Code
  • Breaking Bottlenecks: Applying the Theory of Constraints to Software Development
  • Doris: Unifying SQL Dialects for a Seamless Data Query Ecosystem
  1. DZone
  2. Data Engineering
  3. Databases
  4. Using SQL Census to Audit SQL Server Permissions

Using SQL Census to Audit SQL Server Permissions

SQL Census is a desktop app that inspects your SQL Server and Active Directory to produce a clear overview of each user’s permissions. Read on to learn how it works.

By 
Ally Parker user avatar
Ally Parker
·
Jul. 07, 17 · Tutorial
Likes (1)
Comment
Save
Tweet
Share
2.9K Views

Join the DZone community and get the full member experience.

Join For Free

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 into 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, nine fixed server roles, and nine fixed database roles hang together and condenses these into seven 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 visualizing 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

Published at DZone with permission of Ally Parker, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Automating Data Pipelines: Generating PySpark and SQL Jobs With LLMs in Cloudera
  • Doris: Unifying SQL Dialects for a Seamless Data Query Ecosystem
  • SQL Server Index Optimization Strategies: Best Practices with Ola Hallengren’s Scripts
  • How to Restore a Transaction Log Backup in SQL Server

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!