• Fixes of issues found in version 1.9.4.752

What’s new in this update:

  • Support for SQL Server Management Studio 2014 integration
  • 64-bit architecture native support
  • Improvements of the command line tool and MSBuild and NAnt tasks

 

The step by step guide for creating custom analysis rules with SQL Enlight is available as a .pdf document  here.

If you have any questions, please contact us at our support email or use our feedback form.

 

Message

Avoid altering security within stored procedures

Description

The rule checks and alerts for usage of GRANT, REVOKE, or DENY statements within the body of a stored procedure.
Avoid altering security within stored procedures, functions, and triggers. This can lead to unnecessary database calls, or it can hinder troubleshooting security permissions.

Author

Jeff Foushee

Example

CREATE PROCEDURE testsp_CR0002 (
    @Code VARCHAR(30) = NULL
)
AS

BEGIN
    IF @Code IS NULL
        SELECT * FROM Table1
    ELSE
        SELECT * FROM Table1 WHERE Code like @Code + '%'

    UPDATE MyTable SET Col1 = 'myvalue'

    BEGIN TRAN
        GRANT EXEC ON testsp_CR0002 to myuser
    COMMIT TRAN

    GRANT EXEC ON testsp_CR0002 to myuser  --IGNORE:CR0002

    REVOKE SELECT ON dbo.Table1 TO myuser

    DENY EXECUTE ON testsp_CR0002 to myuser

END

-- this is fine because it is outside of the stored procedure
GRANT EXEC ON testsp_CR0002 to myuser  

Download and try the CR0002 analysis rule.

Message

TOP (100) PERCENT found

Description

This rule checks for the phrase “TOP (100) PERCENT”.
This phrase has no bearing unless the percentage is less than 100.
This phrase is commonly generated by creating a view in the SQL Server View Designer.

Author

Jeff Foushee

Example

SELECT TOP 100 PERCENT
LastName, FirstName, JobTitle, Department
FROM       HumanResources.vEmployeeDepartment
ORDER BY LastName ASC

 

Download and try the CR0001 analysis rule.

 

Until now, only MVPs and trainers are entitled to free SQL Enlight Licenses.  Now everyone who authors a custom analysis rule and shares it with the SQL Server Community can also get a free personal license of SQL Enlight For SSMS worth $145.

What you will have to do?

1. Think of a new useful custom analysis rule and implement it.

2. Write a blog post that describes the practice, which it is enforcing and share the rule’s xml file.

You have your rule and post ready, what’s next?

Simply email your rule to rules@ubitsoft.com or send us link to a blog post describing the rule. Once we check the rule we will give you a free SQL Enlight For SSMS license.

Wondering where to start?

First download and install the latest version of SQL Enlight or SQL Enlight For SSMS.

The Analysis Rule Designer which comes with the tools is not limited by the trial period and you can freely use it to learn create and test your analysis rules.

Here is some resources, which you can use:

More Resources:
We are preparing a step by step tutorial for how to create a custom rule with SQL Enlight and will add a link to it in this post as soon as we have the tutorial published.

If you have any questions, please feel free to contact us.

This is just a maintenance release containing only fixes and improvements.

This is a SQL Enlight For SSMS review by Grant Fritchey, which was published on Simple Talk last week:
https://www.simple-talk.com/sql/sql-tools/sql-enlight-for-sql-server-management-studio/

Grant’s review gives a detail look to the features of the tool and its use.

The article is recommended to anyone as an introduction to the tool.

This week we released an update for SQL Enlight and SQL Enlight for SSMS version 1.9.3.733.
The update include the following improvements common to both editions:

  • Small improvements and fixes
  • Improved Analysis rules selection dialog:

    select-rules-dialog
    The new rules selection dialog adds some useful filters and allows the rule parameters to be set in a single step.

  • Changes specific to SQL Enlight:

    • Added support for Visual Studio 2013

    Changes specific to SQL Enlight For SSMS:

    • NAnt tasks which were missing in the previous version are now included

    Download SQL Enlight and SQL Enlight for SSMS here.

Main components and features supported by each edition:

Features

SQL Enlight

  SQL Enlight For SSMS 

Visual Studio Integration

Yes

No

SQL Server Management Studio Integration

Yes

Yes

Code Formatting

Yes

No

Refactoring

Yes

No

Static Analysis

Yes

Yes

Command Line Tool

Yes

 Yes*

MSBuild tasks

Yes

  Yes**

NAnt tasks

Yes

  Yes**

TFS Check-in Policies

Yes

No

Price

$ 295

$ 195

* Code formatting command is not supported.

** Code formatting tasks are not supported.