SQL Server FAQ - "ALTER LOGIN" - Disabling a Login Name

Home >> FAQs/Tutorials >> SQL Server FAQ

By: FYIcenter.com

How To Disable a Login Name?

If you want temporarily disable a login name, you can use the "ALTER LOGIN" statement with a DISABLE keyword. If you want to enable it later on, you can use the ENABLE keyword. The tutorial exercise below shows how to disable and enable login name "Dba_Login":

-- Login with "sa"

-- Disable a login
ALTER LOGIN Fyi_Login DISABLE;

-- View login status
SELECT name, type, type_desc, is_disabled 
   FROM sys.server_principals
   WHERE type = 'S';
GO
name        type type_desc  is_disabled
----------- ---- ---------- -----------
sa          S    SQL_LOGIN  0
FYI_DBA     S    SQL_LOGIN  0
Dba_Login   S    SQL_LOGIN  1

Now try to login with the disabled login name:

C:\>SQLCMD -S LOCALHOST\SQLEXPRESS -U Dba_Login -P IYF
Msg 18470, Level 14, State 1, Server LOCALHOST\SQLEXPRESS
Login failed for user 'Dba_Login'. Reason: The account is 
   disabled.
C:\>

Run the statements below to enable login name "Dba_Login":

-- Login with "sa"

-- Enable a login
ALTER LOGIN Fyi_Login ENABLE;

(Continued on next topic...)