Reducing risk with Smart Recommendations in SQL Census

One of the most prominent subjects of the last few months within organizations in many industries across the globe has been centered around data security. Organizational compliance with regulations surrounding management of data is increasingly imperative. Implementing measures to safeguard against risks to data security is vital.

SQL Census is a prototype, created by Redgate’s research and development division, Foundry. It was born out of this need for safeguarding and auditing and allows you to trace SQL Server user access across databases within your domain.

Since its launch, we’ve been working closely with a select group of people who have been helping us test and iterate this solution. Our aim is to make sure SQL Census is able to help you trace user access for auditing, troubleshooting, and your daily work around SQL Server permissions.

One of the features that arose from our discussions with users was the idea of being guided to the most obvious issues and getting help with reconciliation. We’re doing that with what we’re calling Smart Recommendations. They are designed to highlight potential risks based on best practices and common occurrences of situations requiring special attention.

With that in mind we have released two Smart Recommendations to date, both of which focus on the ‘who has access to what’ story of data security compliance.

Orphaned users

An orphaned user can be defined as a database user for which the corresponding SQL Server login is either dropped, undefined or incorrectly defined. This can cause issues when required to verify ownership of database access, especially during audits.

A user can be orphaned in the following ways:

  • Dropping a SQL Login without dropping the associated users, when using the SSMS GUI a warning for this is always provided.

  • When using mixed authentication mode, restoring a database backup to another SQL Server instance.
  • After a database mirroring failover.

SQL Census will highlight any orphaned users with a red shield and provide you with a recommended action. This is visible from the users Capability Card on the right hand side of the application window. As you can see there are two possible ways to manage these users:

  1. If they are no longer needed, drop them
  2. Link them back to a SQL Server login

Orphaned Windows Auth logins

One way of avoiding the issue of orphaned users is restricting SQL Server authentication to Windows Auth only, which is considered best practice. Using Windows logins with SQL Server achieves single sign-on and simplifies login administration.

The management of Windows users, groups and passwords is dealt with by Windows system administrators through Active Directory. An individual’s access to SQL Server is determined by group membership and specific permissions granted at this level. When an Active Directory principal is removed, the corresponding SQL Server login becomes orphaned. This is an important audit point and will need to be resolved.

SQL Census now highlights any such Windows Auth logins that don’t exist in the default Active Directory domain.

You can now go through the process of removing any database users associated with the logins and then drop the login from the SQL Server instance.

Other changes

Other changes to the app over the last few weeks include:

  • Improving UI performance with pagination for larger results list
  • Collapsible Group Display
  • Quick view permissions with clickable Group Members
  • Fixed an issue with cultural variance
  • Support for SQL Server 2008R2

What’s next?

  • More Smart Recommendations
  • Add support for other domains
  • Research reporting changes over time to permissions
  • Research reporting who is accessing what and when

Try SQL Census for free today. Getting started is simple, just download the app to begin your 30-day free trial.