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

by

Troubleshooting Guide: SQL Server Error 4623 – “ALL Permission Has Been Deprecated”

Why This Error Happens

This error occurs because the GRANT ALL, DENY ALL, or REVOKE ALL syntax has been fully deprecated in SQL Server 2025 and is no longer supported for most securable classes (such as schemas, objects, or databases). Legacy T-SQL scripts, stored procedures, or third-party deployment tools that still reference the ALL permission keyword will trigger this error instead of silently falling back to older behavior. Essentially, SQL Server 2025 now requires explicit permission names (e.g., SELECT, INSERT, UPDATE, DELETE, EXECUTE) rather than the catch-all ALL shorthand that existed for backward compatibility in earlier versions.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Script or Query

  • Check the SQL Server error log or SSMS query window for the exact line number referencing GRANT ALL, DENY ALL, or REVOKE ALL.
  • Run the following to locate recent permission-related activity in the default trace:
  SELECT * FROM sys.fn_trace_gettable(
      (SELECT TOP 1 path FROM sys.traces WHERE is_default = 1), DEFAULT)
  WHERE EventClass = 109;
  • Review deployment scripts, DACPACs, or ORM-generated SQL (e.g., Entity Framework migrations) for legacy ALL statements.

Step 2: Replace ALL with Explicit Permission Sets

  • Update the script to specify individual permissions instead of ALL. For example, replace:
  GRANT ALL ON dbo.Employees TO [DomainUser];

with:

  GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.Employees TO [DomainUser];
  • For stored procedure/execute permissions, use:
  GRANT EXECUTE ON dbo.usp_GetEmployeeData TO [DomainUser];
  • If unsure which permissions were previously implied by ALL, cross-reference sys.fn_builtin_permissions('OBJECT') to see the full list of applicable permissions for that securable class.

Step 3: Update Third-Party Tools and Automation Scripts

  • Check SSMS version (must be 21.x or later) and update via winget upgrade Microsoft.SQLServerManagementStudio on Windows Server 2025 / Windows 11.
  • Update any PowerShell DBA modules (e.g., dbatools) to the latest version since older cmdlets like Set-DbaPermission may still generate ALL syntax:
  Update-Module -Name dbatools -Force
  • Re-run database deployment pipelines (Azure DevOps, Jenkins, etc.) after patching DACPAC/SSDT project references to SQL Server 2025 compatibility level 170.

Step 4: Set Correct Database Compatibility Level

  • Confirm the database isn’t stuck on an older compatibility level that may be triggering mixed syntax parsing:
  ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 170;
  • Restart the SQL Server service via services.msc or:
  Restart-Service -Name 'MSSQLSERVER' -Force

Alternative or Verification Steps

  • Verify the fix by re-running the updated permission script and confirming no Error 4623 appears in the SSMS Messages tab or Windows Event Viewer under Applications and Services Logs > MSSQLSERVER.
  • As an alternative, use the new Permissions Catalog View (sys.database_permissions) to audit and bulk-regenerate explicit GRANT statements for all affected users/roles instead of manually rewriting each script.