Review: ScriptLogic Security Explorer for SQL Server

Security is a source of frustration for DBAs. They’re frequently asked who has what permissions in which databases and servers. Determining this information generally requires custom code and an in-depth understanding of how SQL Server implements security. DBAs are plagued by permissions that aren't documented carefully and change unexpectedly.

SQL Server Management Studio (SSMS) has a few visual tools for working with security principals and permissions, but there’s a major gap in the DBA’s toolset. Security Explorer by ScriptLogic, a Quest Software company, fills this gap. It’s more than just a SQL Server tool. You can use it to manage users and permissions across your entire enterprise, including those in Microsoft SharePoint, Microsoft Exchange, NTFS, and other systems.

Enterprise Security Management

During my first look at Security Explorer, I was surprised at how broad reaching it is. I expected a SQL Server tool and got an enterprise management tool. The ability to use a single search tool to find users and permissions across varying resources is powerful.

DBAs often need to detail a user’s full permissions or clone one user’s permissions for another. These can be time-consuming and tricky tasks to do manually, but this tool lets DBAs perform these tasks with a few clicks and without engaging other administrators.

While the tool provides great depth of functionality, there were aspects of the tool that showed a lack of understanding of how SQL Server works. For example, it didn’t know how to gracefully handle databases that weren’t online or instances of SQL Server that it couldn’t access (e.g., mirroring partners, log shipping secondary databases).

Usability

Security Explorer works with SQL Server 2000 and later. Installation was quick and easy; no special steps were required to get it working. The tool uses a familiar Windows-style interface similar to many popular Windows utilities. I was able to jump right in and start being productive with the tool.

The downside of the familiar interface was that a certain expected functionality wasn’t there. The pop-up menus weren’t context sensitive, and I found myself frustrated by frequent pop-ups that told me it couldn’t do what I wanted for the selected object. In addition, certain aspects of the tool were counter-intuitive, such as permissions granted to a login must be revoked at the server level rather than the login level.

Broad Search

One of my favorite features is the ability to search for users and permissions across the entire enterprise. This solves a common problem that’s raised in compliance audits. Compliancy auditors will ask what permissions users have or who has permissions to objects. With Security Explorer, the answer is just a few clicks away.

The search functionality has bugs and usability issues, though. The search would continually prompt me to provide login credentials for inaccessible instances (offline or SQL Server Denali instances) until I clicked Cancel for each. When I deselected or deleted the inaccessible instances from the Search Scope, the search returned the same errors and prompts. I couldn’t alter the Search Scope until I restarted the application. I wasn’t able to reproduce this bug.

I performed a search for a user account that existed on multiple machines, existed in multiple databases, and even had a custom database role by the same name. Only the logins and the database roles of which the user account was a member were returned. Figure 1 shows Security Explorer’s search UI.

Figure 1: Security Explorer’s search UI

Backup and Restore

The backup and restore capabilities are a must-have feature. Backing up a database before making changes can be time-consuming, and trying to recover permissions from a database backup is troublesome. With Security Explorer, you can back up permissions at any level (server, database, object, or principal) and restore at any level. The scheduling tool can be used to perform regular backups of permissions. You can restore permissions at any granularity.

This feature is highly valuable, but there were usability issues. The backup scheduler didn’t always save changes made to the schedule. It didn’t report or log errors that it encountered when it executed the backup, so a silently failing job could result in permissions not getting backed up.

I was disappointed that the tool’s only automation was for scheduled backups. There are no other command-line tools or APIs available. I was informed that there are plans to add automation features in the future.

Other Bugs

I was unable to get the tool to start if a connection to the domain controller (DC) was unavailable. The error it returned simply said that server wasn’t available. If it was unable to connect to a DC while it was running, the application would freeze.

Final Recommendation

Security Explorer solves many of the problems that DBAs face with SQL Server security. I think any DBA would find this tool useful and a real time-saver. DBAs who deal with compliance regulations will find this tool indispensible. The limitations and bugs are minimal and tolerable. The price tag is sized right, too.