MS SQL Server Interview Question:

How To Disable a Login Name in MS SQL Server?

Submitted by: Administrator

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 ggl_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
ggl_DBA S SQL_LOGIN 0
Dba_Login S SQL_LOGIN 1

Now try to login with the disabled login name:

C:>SQLCMD -S LOCALHOSTSQLEXPRESS -U Dba_Login -P IYF
Msg 18470, Level 14, State 1, Server LOCALHOSTSQLEXPRESS
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 ggl_Login ENABLE;
Submitted by: Administrator