How To Disable a Login Name in MS SQL Server?
Submitted by: AdministratorIf 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
-- 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
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | How to download Microsoft SQL Server 2005 Express Edition? |
☺ | What Are Binary String Data Types in MS SQL Server? |
☺ | What Happens If Strings Are Casted into Wrong Code Pages in MS SQL Server? |
☺ | Can You Roll Back the DDL Statement in a Trigger? |
☺ | How To Declare and Use Cursor Variables? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |
☺ | Sybase Interview Questions. |