SA0217 : Usage of GRANT,DENY and REVOKE statement with ALL option is deprecated

Introduction

The use of the deprecated GRANT ALL, DENY ALL, and REVOKE ALL statements in T-SQL code can lead to potential compatibility issues in future SQL Server versions.

Description

Using deprecated statements like GRANT ALL , DENY ALL , and REVOKE ALL can lead to unforeseen issues in database security and maintainability. These statements are considered outdated and may not provide the desired granularity in permission handling as newer alternatives.

For example:

-- Example of deprecated usage
GRANT ALL ON schema::Sales TO user1;
DENY ALL ON schema::Sales TO user1;
REVOKE ALL ON schema::Sales FROM user1;

The use of these statements is problematic because it affects all actions on an object, which might be too permissive or restrictive. They also lack clarity, making it hard to understand the specific permissions granted or denied.

Grants or denies can become too broad, impacting unintended areas of a database.
Maintaining code with deprecated statements is difficult as precise permissions are not evident, leading to potential security risks.

How to fix

Use specific permissions with GRANT , DENY , and REVOKE statements to replace deprecated usage of GRANT ALL , DENY ALL , and REVOKE ALL .

Follow these steps to address the issue:

1.Identify the specific permissions needed for the schema or object that were previously granted using GRANT ALL , DENY ALL , or REVOKE ALL .

2.Replace the deprecated statements with individual permission grants using GRANT , DENY , or REVOKE for each specific permission.

3.Apply these changes within your T-SQL scripts or using SQL Server Management Studio (SSMS) to ensure permissions are clearly defined.

4.Verify that the permissions are correctly set by testing the access levels of users or roles impacted by the changes.

For example:

-- Instead of using GRANT ALL
GRANT SELECT, INSERT, UPDATE ON schema::Sales TO user1;

-- Instead of using DENY ALL
DENY DELETE ON schema::Sales TO user1;

-- Instead of using REVOKE ALL
REVOKE INSERT ON schema::Sales FROM user1;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

8 minutes per issue.

Additional Information

Deprecated Database Engine Features in SQL Server 2017

Example Test SQL

REVOKE VIEW DEFINITION ON ROLE::SammamishParking
    FROM JinghaoLiu CASCADE;

REVOKE ALL ON ROLE::SammamishParking
    FROM JinghaoLiu CASCADE;

GRANT ALL TO AuditMonitor


DENY ALL ON ROLE::SammamishParking
    TO JinghaoLiu CASCADE;

Analysis Results

	Message	Line	Column
1	SA0217 : Usage of GRANT,DENY and REVOKE statement with ALL option is deprecated.	4	7
2	SA0217 : Usage of GRANT,DENY and REVOKE statement with ALL option is deprecated.	7	6
3	SA0217 : Usage of GRANT,DENY and REVOKE statement with ALL option is deprecated.	10	5

Analysis Rules