Help: SQL Server

This was one of the interesting question raised in MSDN forum. Since it was an interesting one, it qualifies for a blog.

Question

In Login Properties screen, under Status page there are two options for a login.

• Permission to connect to database engine
  • ENABLE
  • DISABLE
• Login
  • GRANT
  • DENY

What does that mean? Both would run different T-SQL but end effect is that user would not be able to connect. Why two “similar” options?

Answer

As said in question, there are two different T-SQL. Enabled / Disabled would call below T-SQL

ALTER LOGIN [MyLogin] DISABLE
GO
ALTER LOGIN [MyLogin] ENABLE
GO

And other option would be Grant / Deny would run below T-SQL

GRANT CONNECT SQL TO [MyLogin]
GO
DENY CONNECT SQL TO [MyLogin]
GO

Here is the difference.

1. ALTER LOGIN [MyLogin] DISABLE will block the login from connecting to SQL Server. On the other hand, DENY CONNECT SQL will NOT block members of the sysadmin fixed server role from logging in because deny do NOT apply to sysadmins.
2. Other use case is that disabled logins can be impersonated via execute as login = ‘login_name’ but they can’t directly connect.

SQL Server Errorlog would have different message as below:

For disabled login below message.

2014-01-24 12:30:06.790 Logon        Error: 18470, Severity: 14, State: 1.

2014-01-24 12:30:06.790 Logon        Login failed for user ‘MyLogin’. Reason: The account is disabled. [CLIENT: <local machine>]

For login which don’t have connect permission

2014-01-24 12:29:50.320 Logon        Error: 18456, Severity: 14, State: 12.

2014-01-24 12:29:50.320 Logon        Login failed for user ‘MyLogin’. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]

End user would get different message:

For disabled login.

TITLE: Connect to Server

——————————

Cannot connect to (local).

——————————

ADDITIONAL INFORMATION:

Login failed for user ‘MyLogin’. Reason: The account is disabled. (Microsoft SQL Server, Error: 18470)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18470&LinkId=20476

——————————

BUTTONS:

OK

——————————

And while connecting via DENY permission:

TITLE: Connect to Server

——————————

Cannot connect to (local).

——————————

ADDITIONAL INFORMATION:

Login failed for user ‘MyLogin’. (Microsoft SQL Server, Error: 18456)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

——————————

BUTTONS:

OK

——————————

Hope you have learned something new. At least I did.

Original post: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/8f72665a-b95d-402f-ad53-36d59d6a8f44/