none
Login Status

    Question

  • Hi,

    Can anyone help me to understand this,

    In login setting we are able to see two options

    1.Permission to connect to database engine

    Grant or Deny

    2. Login:

    Enabled

    Disabled

    What is difference between these?

    Thanks

    Friday, January 24, 2014 6:27 AM

Answers

All replies

  • from thsi thread

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f41feb6d-62ec-424f-8b25-7e6ef1da10e2/difference-between-permission-to-connect-database-engine-grantdeny-loginenabled-disabled?forum=sqldatabaseengine

    In order to successfully login, you'd need to both Grant permission to connect to the database engine and Enable the login.

    Granting permission to the DB Engine basically says this login can connect to this server instance...enabling/disabling a login does just that - enables or disables it's ability to login to the instance.

    If you still have trouble after this, also take a look at the default database for the login and make sure the login has permission to access that database.  For more information, see the following topics in BOL:

    CREATE LOGIN (server level login to allow permission at the instance level)

    CREATE USER (database level user that maps to a login to allow permissions at a particular database level)

    Once you have the user in a database, you'll need to grant permissions to that user to allow it to do whatever you are trying to do with it (i.e. select, update, delete, etc.)


    Satheesh
    My Blog


    Friday, January 24, 2014 6:36 AM
    1. ALTER LOGIN DISABLE will block login from connecting to sql server. DENY CONNECT will NOT block members of the sysadmin fixed server role from logging in because denys do NOT apply to sysadmins.
    2. Disabled logins can be impersonated via execute as login = 'login_name' but they can't directly connect.

    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Friday, January 24, 2014 6:44 AM
    Moderator
  • Shashikala

    In short, both behaves almost same from user perspective except different error message you will notice such as when "Deny" is ON then it will prompt for "Login failed for user...."  and when "Disabled" is ON then it will prompt for "Login failed for user... Reason, account is disabled".

    By definition from TechNet (http://technet.microsoft.com/en-us/library/ms180015(v=sql.105).aspx):

    Permission to connect to database engine

    When you work with this setting, you should think of the selected login as a principal that can be granted or denied permission on a securable.

    Select Grant to grant CONNECT SQL permission to the login. Select Deny to deny CONNECT SQL to the login.

    Login

    When you work with this setting, you should think of the selected login as a record in a table. Changes to the values listed here will be applied to the record.

    A login that has been disabled continues to exist as a record. But if it tries to connect to SQL Server, the login will not be authenticated.

    Select this option to enable or disable this login. This option uses the ALTER LOGIN statement with the either ENABLE or DISABLE option.


    Thanks, Mohan Kumar - Please mark the post as answered if it answers your question.

    Friday, January 24, 2014 6:47 AM
  • In short, both behaves almost same from user perspective except different error message you will notice such as when "Deny" is ON then it will prompt for "Login failed for user...." 

    Except SysAdmins

    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Friday, January 24, 2014 6:57 AM
    Moderator
  • Just blogged http://sqlserver-help.com/2014/01/24/question-what-is-the-difference-between-disabled-login-and-deny-connect-permission/

    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    • Proposed as answer by HuaMin Chen Friday, January 24, 2014 7:27 AM
    • Marked as answer by tracycaiModerator Wednesday, January 29, 2014 9:19 AM
    Friday, January 24, 2014 7:13 AM
    Moderator
  • Hi Balmukund

    Agreed as Sysadmins are exception to "Deny" option.

    However, Sysadmins cannot login if "Login" property is turned on "Disabled" then they cannot login.


    Thanks, Mohan Kumar - Please mark the post as answered if it answers your question.

    Friday, January 24, 2014 7:14 AM
  • Hi Balmukund

    Agreed as Sysadmins are exception to "Deny" option.

    However, Sysadmins cannot login if "Login" property is turned on "Disabled" then they cannot login.


    Thanks, Mohan Kumar - Please mark the post as answered if it answers your question.

    yes. that's what I have written. "ALTER LOGIN DISABLE will block login from connecting to sql server"

    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Friday, January 24, 2014 7:20 AM
    Moderator