Thursday, April 20, 2023

Error 33129: Cannot use ALTER LOGIN with the ENABLE or DISABLE argument for a Windows group.

Recently an incredbily wise DBA friend of mine -- really -- attempted to disable the BUILTIN\Administrators login in SQL Server with this statement:

     ALTER LOGIN [BUILTIN\Administrators] DISABLE

     GO

But it failed with this message (user name dummified):

Message

Executed as user: domain\svc_account. Cannot use ALTER LOGIN with the ENABLE or DISABLE argument for a Windows group. GRANT or REVOKE the CONNECT SQL permission instead. [SQLSTATE 42000] (Error 33129).  The step failed.

Per BOL, "The login of a Windows Group cannot be disabled. To temporarily remove access permission granted to a Windows Group, REVOKE the CONNECT permission of the login for the Windows Group. Windows users might still have access through their individual login or through another Windows Group." 


So my incredibly talented DBA friend changed their statement to this, and the members of this Windows Group can no longer connect to SQL Server:

    REVOKE CONNECT SQL TO [BUILTIN\Administrators];


NOTE:  Don't miss that underlined piece about any member of that Windows Group can still get in if they are members of any other Windows Group.


More information on Error 33129:

https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-33129-database-engine-error?view=sql-server-ver16


No comments:

Post a Comment