locked
Login failed for user RRS feed

  • Question

  • When I tried to login to Database I got error "Login failed for user "

    When I saw in Errorlogs I got following:

    Login failed for user 'Domain\sqldev'.

    2010-07-05 02:40:56.25 Logon       Error: 18456, Severity: 14, State: 11.

     

    This state 11 correspond to Login is valid, but server access failed.

    It seems that  user do not have server access.

    How to grant server access to user in SQL. Let me know any configuration settings require for this to work

     

    Monday, July 5, 2010 10:57 AM

Answers

  • declare @sql varchar(100)

    set @sql = 'GRANT CONNECT SQL TO ' +
    QUOTENAME(@login_name)
    execute( @sql )

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, July 5, 2010 11:13 AM
  • Harshadh,

    You are right !

    STATE 11 &12:

    This state means the domain login trying to access sql server that wasn’t explicitly added to sql server .Even though it can access OS, it can’t login in to sql server. To overcome this error, you can add that domain login to sql logins explicitly provided you have create login permission.

    Further to know about the the 18456 and the states i request u to refer the following link .
    http://sql-articles.com/articles/troubleshooting/troubleshooting-login-failed-error-18456

     

    The following statement creates login from windows domain account
    CREATE LOGIN [<domainName>\<loginName>] FROM WINDOWS;

    ---------------
    Thanks,Suhas V

    Monday, July 5, 2010 12:56 PM

All replies

  • declare @sql varchar(100)

    set @sql = 'GRANT CONNECT SQL TO ' +
    QUOTENAME(@login_name)
    execute( @sql )

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, July 5, 2010 11:13 AM
  • Harshadh,

    You are right !

    STATE 11 &12:

    This state means the domain login trying to access sql server that wasn’t explicitly added to sql server .Even though it can access OS, it can’t login in to sql server. To overcome this error, you can add that domain login to sql logins explicitly provided you have create login permission.

    Further to know about the the 18456 and the states i request u to refer the following link .
    http://sql-articles.com/articles/troubleshooting/troubleshooting-login-failed-error-18456

     

    The following statement creates login from windows domain account
    CREATE LOGIN [<domainName>\<loginName>] FROM WINDOWS;

    ---------------
    Thanks,Suhas V

    Monday, July 5, 2010 12:56 PM