How to fix Error 4628 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 4628 – Deprecated ALL Permission

Why This Error Happens

This warning occurs because the ALL permission keyword used in GRANT, DENY, or REVOKE statements has been deprecated since early SQL Server versions and is retained purely for backward compatibility with legacy scripts. SQL Server 2025 raises Error 4628 because ALL does not actually grant every permission available on the target entity (table, view, stored procedure, etc.) — it only maps to a small, fixed subset of legacy permissions (SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES). Administrators relying on ALL for comprehensive access control often end up with incomplete permission sets, leading to unexpected access-denied errors elsewhere in the application.

Step-by-Step Resolution Guide

Step 1: Identify the Script or Object Triggering the Warning

  • Check SQL Server Error Logs via SSMS > Management > SQL Server Logs or run:
  EXEC sp_readerrorlog 0, 1, 'ALL';
  • Search deployment/migration scripts, stored procedures, or ORM-generated SQL (e.g., Entity Framework migrations) for the keyword GRANT ALL, DENY ALL, or REVOKE ALL.
  • Note the schema and object name referenced in the statement throwing Error 4628.

Step 2: Replace ALL with Explicit Permission Statements

  • Open the offending script in SSMS or Azure Data Studio.
  • Replace the generic statement:
  GRANT ALL ON dbo.Employees TO [AppUser];

with explicit permissions matching your intended access:

  GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.Employees TO [AppUser];
  • For execute-level access on procedures, use:
  GRANT EXECUTE ON dbo.usp_UpdateEmployee TO [AppUser];
  • Save the corrected script and re-run it in the target database.

Step 3: Audit Existing Role and User Permissions

  • Run the following query to list all current permissions tied to the affected user/role:
  SELECT grantee_principal_id, permission_name, state_desc, class_desc
  FROM sys.database_permissions
  WHERE major_id = OBJECT_ID('dbo.Employees');
  • Cross-reference this output against your application’s actual data access requirements (CRUD operations, procedure execution, etc.).
  • Manually add any missing permissions (e.g., REFERENCES, VIEW DEFINITION) that ALL does not cover but your application needs.

Step 4: Update Deployment Pipelines and Documentation

  • Search CI/CD pipeline scripts (DACPAC, Flyway, Liquibase, or custom .sql deployment files) for legacy ALL usage.
  • Update version-controlled schema scripts in your repository to reflect explicit permission grants.
  • Document the specific permission set per role in your internal wiki to prevent future regressions when team members reuse old templates.

Alternative or Verification Steps

  • Verify the fix by querying sys.database_permissions on the affected object after redeployment — confirm each required permission (SELECT, INSERT, UPDATE, DELETE, EXECUTE) appears individually rather than relying on ALL.
  • If rewriting every script is not immediately feasible, suppress the warning at the session level using SET NOCOUNT ON combined with error-level filtering in your logging tool, but treat this as a temporary workaround only — the underlying permission gaps must still be audited and corrected.