SA0166 : Avoid altering security within stored procedures |
![]() |
Avoid incorporating security statements like GRANT, REVOKE, or DENY within the body of stored procedures.

Security management statements inside stored procedures can create problems in SQL Server environments. These statements manipulate permissions in ways that might not be immediately clear, potentially leading to security issues that are hard to track and resolve.
For example:
1-- Problematic use of security statements 2CREATE PROCEDURE UpdatePermissions 3AS 4BEGIN 5 GRANT SELECT ON dbo.Employees TO UserRole; 6END
In this case, embedding GRANT within a stored procedure can obscure permission assignments, complicating auditing and maintenance. Such practices can prompt multiple unnecessary database calls and complicate troubleshooting when permissions are modified unexpectedly during procedure execution.
-
Hidden security changes can make auditing difficult.
-
Increased risk of accidental permission changes when procedures are altered.

Refactor stored procedures to remove embedded security management statements like GRANT, REVOKE, or DENY to enhance security and maintainability.
Follow these steps to address the issue:
-
Identify stored procedures that include embedded security statements such as GRANT, REVOKE, or DENY.
-
Refactor the stored procedures by removing security statements from the procedure body.
-
Manage security permissions separately using dedicated scripts or SQL Server Management Studio (SSMS) to apply permissions at the database level.
-
Implement auditing practices to monitor permission changes and ensure comprehensive security management.
For example:
1-- Original stored procedure with embedded security statement 2CREATE PROCEDURE UpdatePermissions 3AS 4BEGIN 5 GRANT SELECT ON dbo.Employees TO UserRole; 6END 7 8-- Refactored approach with separate security management 9-- Stored procedure without security statement 10CREATE PROCEDURE UpdatePermissions 11AS 12BEGIN 13 -- Procedure logic here 14END 15 16-- Separate script to manage permissions 17GRANT SELECT ON dbo.Employees TO UserRole;

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

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.


Design Rules, Security Rules

There is no additional info for this rule.

SQL
1CREATE PROCEDURE testsp_SA0200 ( 2 @Code VARCHAR(30) = NULL 3) 4AS 5BEGIN 6 IF @Code IS NULL 7 SELECT * FROM Table1 8 ELSE 9 SELECT * FROM Table1 WHERE Code like @Code + '%' 10 11 UPDATE MyTable SET Col1 = 'myvalue' 12 13 BEGIN TRAN 14 GRANT EXEC ON testsp_SA0200 to myuser 15 COMMIT TRAN 16 17 GRANT EXEC ON testsp_SA0200 to myuser --IGNORE:SA0166 18 19 REVOKE SELECT ON dbo.Table1 TO myuser 20 21 DENY EXECUTE ON testsp_SA0200 to myuser 22 23END 24 25-- this is ignored, because it is reported by SA0150 26GRANT EXEC ON testsp_SA0200 to myuser |

Message | Line | Column | |
---|---|---|---|
1 | SA0166 : Avoid altering security within stored procedures. | 14 | 8 |
2 | SA0166 : Avoid altering security within stored procedures. | 19 | 4 |
3 | SA0166 : Avoid altering security within stored procedures. | 21 | 4 |
