locked
SQL Server Login Issue RRS feed

  • Question

  • Hi

    I have created UserGroup Group1 and added few logins.

    I connected to sql server as ‘Sa’ and created login with the group name with db_owner rights ( I have choosen a particular db as default database).

    But the user’s are not able to login and sql server is throwing error

    Cannot Connect SQL Server.  Cannot Open user default database. Login failed.

     

    Need your help resolving this issue.

     

    Thanks in advance.

     

    Tuesday, March 9, 2010 7:19 AM

Answers

All replies

  • Make sure you set the default database to master or other databases that are available in the SQL server.
    ALTER LOGIN [AM] WITH DEFAULT_DATABASE  = [master]
    Whenever a login is coming through to SQL server its initial state is identified by the default database and then allowed to SQL server.
    Thanks, Leks
    Tuesday, March 9, 2010 7:24 AM
    Answerer
  • Hi

    Seems like you forgot to map the database to the login during the login create time.
    Connect to server as "sa" and expand Server->Security->Logins-> <particular login> and launch properties from the context menu and click the "User Mapping" page
    Check the default database and assign the user and click ok.

    Now you can try to verify connecting to it.

    -Sreekar
    Tuesday, March 9, 2010 7:25 AM
  • Thanks Leks.  I have choosen one of the database as default.  Login is having db_owner rights.
    Tuesday, March 9, 2010 7:36 AM
  • Thanks Sreekar

    Yes I have given the mapping.  But still it is not allowing user's to login.
    Tuesday, March 9, 2010 7:37 AM
  • Can you post the complete error message that is recorded in errorlog for this particular login failure?
    Thanks, Leks
    Tuesday, March 9, 2010 7:47 AM
    Answerer
  •  

    Cannot connect to SQL1.

     

    ===================================

     

    Cannot open user default database. Login failed.

    Login failed for user 'NetworkName\User1'. (.Net SqlClient Data Provider)

     

    ------------------------------

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

     

    ------------------------------

    Server Name: SQL1

    Error Number: 4064

    Severity: 11

    State: 1

    Line Number: 65536

     

     

    ------------------------------

    Program Location:

     

       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

       at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)

       at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)

       at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)

       at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)

       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)

       at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)

       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

       at System.Data.SqlClient.SqlConnection.Open()

       at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)

       at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

    Tuesday, March 9, 2010 8:41 AM
  • Hi,

    Have you seen this article? http://support.microsoft.com/kb/307864

    I hope it helps.
    J.
    There are 10 type of people. Who knows binary and who does not.
    Monday, March 15, 2010 6:43 AM