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.
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:
- If they are no longer needed, drop them
- 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 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
- 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.
Was this article helpful?
Also in Blog
A lot of work with Flyway is going to take place on development machines with the Flyway command line installed. Another healthy chunk of the work will be on dedicated instances used for Continuous In...
Also in Audit & Compliance
It’s just over two years since the GDPR started being enforced and it’s also the month when many businesses in the US now need to comply with the CCPA. So it's an opportune time to talk about one ...
Also about Foundry
In the Foundry Team at Redgate, we’ve been looking into how data professionals manage the complexities of their wider data estates. One aspect of this is configuring, deploying and monitoring dr...
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...
Also about data protection and privacy
This article describes the practicalities of data masking, the various methods we can use, and the potential pitfalls. In subsequent articles, I'll demonstrate how we can mask, or sanitize, different ...