title: SQL User with sysadmin role can't login to database
body: <p>I have a domain user account that I use to run a particular ASP.NET web application it is NETOPS\websvr.  This domain account is setup in the SQL Server in question and is in the sysadmin role.  However, I randomly get errors stating that the login failed for he user.  Sometimes it is on this database and others it is on a different database on the same server.</p>
  
  <p>I have been searching the web for the past several hours trying to find an answer for this and I am no closer to an answer than when I started so I thought I would ask here to see if anyone has any ideas.  Below is a output from my instance of Stackexchange.Exceptional</p>
  
  <pre>
  Exceptions Log: WebSync
  Cannot open database "alsmi" requested by the login. The login failed. Login failed for user 'NETOPS\websvr'.
  System.Data.SqlClient.SqlException
  System.Data.SqlClient.SqlException (0x80131904): Cannot open database "alsmi" requested by the login. The login failed.
  Login failed for user 'NETOPS\websvr'.
     at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
     at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
     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, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
     at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
     at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
     at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData)
     at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
     at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
     at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
     at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
     at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
     at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
     at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
     at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
     at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
     at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
     at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
     at System.Data.SqlClient.SqlConnection.Open()
     at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteScalar(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters)
     at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteScalar(String connectionString, CommandType commandType, String commandText)
     at GetPatchLevel.ProcessRequest(HttpContext context)
  ClientConnectionId:e33983c0-da22-4050-a894-55274b46f645
  
  Full Trace:
  
  ProcessRequest at offset 424 in file:line:column :0:0
  System.Web.HttpApplication.IExecutionStep.Execute at offset 342 in file:line:column :0:0
  ExecuteStep at offset 70 in file:line:column :0:0
  ResumeSteps at offset 1085 in file:line:column :0:0
  BeginProcessRequestNotification at offset 96 in file:line:column :0:0
  ProcessRequestNotificationPrivate at offset 187 in file:line:column :0:0
  ProcessRequestNotificationHelper at offset 727 in file:line:column :0:0
  ProcessRequestNotification at offset 31 in file:line:column :0:0
  MgdIndicateCompletion at offset 0 in file:line:column :0:0
  ProcessRequestNotificationHelper at offset 1110 in file:line:column :0:0
  ProcessRequestNotification at offset 31 in file:line:column :0:0
  
  
  occurred 2 hours ago on web1 (delete)
  Server Variables
  CONTENT_LENGTH  0
  HTTP_X_FORWARDED_FOR    0.0.0.0
  HTTP_X_SSL_CIPHER   AES128-SHA SSLv3 Kx=RSA Au=RSA Enc=AES(128) Mac=SHA
  LOCAL_ADDR  192.168.1.117
  REMOTE_ADDR 192.168.1.216
  REMOTE_HOST 192.168.1.216
  REQUEST_METHOD  GET
  URL /Services/GetPatchLevel.ashx
  URL and Query   http://website.net/Services/GetPatchLevel.ashx?dbname=alsmi&username=0000&pwd=0000&clientid=0000
  
  Custom
  SQL-Server  sqlserver1
  SQL-ErrorNumber 4060
  SQL-LineNumber  65536
  
  QueryString
  clientid    0000
  dbname  alsmi
  pwd 0000
  username    0000
  
  RequestHeaders
  Host    website.net
  X-Forwarded-For 0.0.0.0
  X-SSL-cipher    AES128-SHA SSLv3 Kx=RSA Au=RSA Enc=AES(128) Mac=SHA
  Exceptional 1.0.0.111
  SQL Error Store
  Server time is 1/30/2015 5:50:04 PM
  </pre>
  
  <p>Here is the code that generates the connection string for the database.</p>
  
  <pre><code>   public static string GetConnString(string DBName)
  {
      string retval = "";
      WebSyncDatabase db = WebSyncDatabase.GetDatabase(DBName);
      SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
      csb.DataSource = db.DBServer;
      csb.InitialCatalog = db.DBName;
      csb.IntegratedSecurity = true;
      csb.ConnectTimeout = 25;
      csb.MaxPoolSize = 150;
      retval = csb.ConnectionString;
      return retval;
  }
  </code></pre>
  
  <p>Here is the data access code.  The SqlHelper.ExecuteScalar is from Microsoft Data Access Application Block for .NET code unmodified.  he below code literally works tens of millions of times per day for us with random exceptions.</p>
  
  <pre><code> string connString = WebSyncUtils.GetConnString(dbName);
          switch (context.Request.HttpMethod.ToUpper())
          {
              case "GET":
                  int retval = 0;
                  try
                  {
                      retval = (int)SqlHelper.ExecuteScalar(connString, CommandType.Text, "SELECT PatchLevel FROM PatchStatus");
                  }
                  catch (Exception ex)
                  {
                      WebSyncUtils.LogMessage(string.Format("Get Next Patch Level for {0} {1} {2}", dbName, userName, pwd), ex);
                  }
                  context.Response.Write(retval);
                  break;
              case "POST":
                  break;
              default:
                  break;
          }
  </code></pre>
  
  <p>more code sample for Methodman</p>
  
  <pre><code>  /// &lt;summary&gt;
      /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the database specified in 
      /// the connection string using the provided parameter values.  This method will query the database to discover the parameters for the 
      /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
      /// &lt;/summary&gt;
      /// &lt;remarks&gt;
      /// This method provides no access to output parameters or the stored procedure's return value parameter.
      /// 
      /// e.g.:  
      ///  int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36);
      /// &lt;/remarks&gt;
      /// &lt;param name="connectionString"&gt;A valid connection string for a SqlConnection&lt;/param&gt;
      /// &lt;param name="spName"&gt;The name of the stored procedure&lt;/param&gt;
      /// &lt;param name="parameterValues"&gt;An array of objects to be assigned as the input values of the stored procedure&lt;/param&gt;
      /// &lt;returns&gt;An object containing the value in the 1x1 resultset generated by the command&lt;/returns&gt;
      public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)
      {
          if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
          if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );
  
          // If we receive parameter values, we need to figure out where they go
          if ((parameterValues != null) &amp;&amp; (parameterValues.Length &gt; 0)) 
          {
              // Pull the parameters for this stored procedure from the parameter cache (or discover them &amp; populate the cache)
              SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
  
              // Assign the provided values to these parameters based on parameter order
              AssignParameterValues(commandParameters, parameterValues);
  
              // Call the overload that takes an array of SqlParameters
              return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
          }
          else 
          {
              // Otherwise we can just call the SP without params
              return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
          }
      }
  
      /// &lt;summary&gt;
      /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection. 
      /// &lt;/summary&gt;
      /// &lt;remarks&gt;
      /// e.g.:  
      ///  int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount");
      /// &lt;/remarks&gt;
      /// &lt;param name="connection"&gt;A valid SqlConnection&lt;/param&gt;
      /// &lt;param name="commandType"&gt;The CommandType (stored procedure, text, etc.)&lt;/param&gt;
      /// &lt;param name="commandText"&gt;The stored procedure name or T-SQL command&lt;/param&gt;
      /// &lt;returns&gt;An object containing the value in the 1x1 resultset generated by the command&lt;/returns&gt;
      public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText)
      {
          // Pass through the call providing null for the set of SqlParameters
          return ExecuteScalar(connection, commandType, commandText, (SqlParameter[])null);
      }
  
      /// &lt;summary&gt;
      /// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection 
      /// using the provided parameters.
      /// &lt;/summary&gt;
      /// &lt;remarks&gt;
      /// e.g.:  
      ///  int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
      /// &lt;/remarks&gt;
      /// &lt;param name="connection"&gt;A valid SqlConnection&lt;/param&gt;
      /// &lt;param name="commandType"&gt;The CommandType (stored procedure, text, etc.)&lt;/param&gt;
      /// &lt;param name="commandText"&gt;The stored procedure name or T-SQL command&lt;/param&gt;
      /// &lt;param name="commandParameters"&gt;An array of SqlParamters used to execute the command&lt;/param&gt;
      /// &lt;returns&gt;An object containing the value in the 1x1 resultset generated by the command&lt;/returns&gt;
      public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
      {
          if( connection == null ) throw new ArgumentNullException( "connection" );
  
          // Create a command and prepare it for execution
          SqlCommand cmd = new SqlCommand();
  
          bool mustCloseConnection = false;
          PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );
  
          // Execute the command &amp; return the results
          object retval;
  
          try
          {
              retval = cmd.ExecuteScalar();
          }
          finally
          {
              // Detach the SqlParameters from the command object, so they can be used again
              cmd.Parameters.Clear();
  
              if (mustCloseConnection)
                  connection.Close();
          }           
  
          return retval;
      }
  
      /// &lt;summary&gt;
      /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection 
      /// using the provided parameter values.  This method will query the database to discover the parameters for the 
      /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
      /// &lt;/summary&gt;
      /// &lt;remarks&gt;
      /// This method provides no access to output parameters or the stored procedure's return value parameter.
      /// 
      /// e.g.:  
      ///  int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", 24, 36);
      /// &lt;/remarks&gt;
      /// &lt;param name="connection"&gt;A valid SqlConnection&lt;/param&gt;
      /// &lt;param name="spName"&gt;The name of the stored procedure&lt;/param&gt;
      /// &lt;param name="parameterValues"&gt;An array of objects to be assigned as the input values of the stored procedure&lt;/param&gt;
      /// &lt;returns&gt;An object containing the value in the 1x1 resultset generated by the command&lt;/returns&gt;
      public static object ExecuteScalar(SqlConnection connection, string spName, params object[] parameterValues)
      {
          if( connection == null ) throw new ArgumentNullException( "connection" );
          if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );
  
          // If we receive parameter values, we need to figure out where they go
          if ((parameterValues != null) &amp;&amp; (parameterValues.Length &gt; 0)) 
          {
              // Pull the parameters for this stored procedure from the parameter cache (or discover them &amp; populate the cache)
              SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
  
              // Assign the provided values to these parameters based on parameter order
              AssignParameterValues(commandParameters, parameterValues);
  
              // Call the overload that takes an array of SqlParameters
              return ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
          }
          else 
          {
              // Otherwise we can just call the SP without params
              return ExecuteScalar(connection, CommandType.StoredProcedure, spName);
          }
      }
  
      /// &lt;summary&gt;
      /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlTransaction. 
      /// &lt;/summary&gt;
      /// &lt;remarks&gt;
      /// e.g.:  
      ///  int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount");
      /// &lt;/remarks&gt;
      /// &lt;param name="transaction"&gt;A valid SqlTransaction&lt;/param&gt;
      /// &lt;param name="commandType"&gt;The CommandType (stored procedure, text, etc.)&lt;/param&gt;
      /// &lt;param name="commandText"&gt;The stored procedure name or T-SQL command&lt;/param&gt;
      /// &lt;returns&gt;An object containing the value in the 1x1 resultset generated by the command&lt;/returns&gt;
      public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText)
      {
          // Pass through the call providing null for the set of SqlParameters
          return ExecuteScalar(transaction, commandType, commandText, (SqlParameter[])null);
      }
  
      /// &lt;summary&gt;
      /// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction
      /// using the provided parameters.
      /// &lt;/summary&gt;
      /// &lt;remarks&gt;
      /// e.g.:  
      ///  int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
      /// &lt;/remarks&gt;
      /// &lt;param name="transaction"&gt;A valid SqlTransaction&lt;/param&gt;
      /// &lt;param name="commandType"&gt;The CommandType (stored procedure, text, etc.)&lt;/param&gt;
      /// &lt;param name="commandText"&gt;The stored procedure name or T-SQL command&lt;/param&gt;
      /// &lt;param name="commandParameters"&gt;An array of SqlParamters used to execute the command&lt;/param&gt;
      /// &lt;returns&gt;An object containing the value in the 1x1 resultset generated by the command&lt;/returns&gt;
      public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
      {
          if( transaction == null ) throw new ArgumentNullException( "transaction" );
          if( transaction != null &amp;&amp; transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
  
          // Create a command and prepare it for execution
          SqlCommand cmd = new SqlCommand();
          bool mustCloseConnection = false;
          PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection );
  
          // Execute the command &amp; return the results
          object retval = cmd.ExecuteScalar();
  
          // Detach the SqlParameters from the command object, so they can be used again
          cmd.Parameters.Clear();
          return retval;
      }
  
      /// &lt;summary&gt;
      /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified
      /// SqlTransaction using the provided parameter values.  This method will query the database to discover the parameters for the 
      /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
      /// &lt;/summary&gt;
      /// &lt;remarks&gt;
      /// This method provides no access to output parameters or the stored procedure's return value parameter.
      /// 
      /// e.g.:  
      ///  int orderCount = (int)ExecuteScalar(trans, "GetOrderCount", 24, 36);
      /// &lt;/remarks&gt;
      /// &lt;param name="transaction"&gt;A valid SqlTransaction&lt;/param&gt;
      /// &lt;param name="spName"&gt;The name of the stored procedure&lt;/param&gt;
      /// &lt;param name="parameterValues"&gt;An array of objects to be assigned as the input values of the stored procedure&lt;/param&gt;
      /// &lt;returns&gt;An object containing the value in the 1x1 resultset generated by the command&lt;/returns&gt;
      public static object ExecuteScalar(SqlTransaction transaction, string spName, params object[] parameterValues)
      {
          if( transaction == null ) throw new ArgumentNullException( "transaction" );
          if( transaction != null &amp;&amp; transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
          if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );
  
          // If we receive parameter values, we need to figure out where they go
          if ((parameterValues != null) &amp;&amp; (parameterValues.Length &gt; 0)) 
          {
              // PPull the parameters for this stored procedure from the parameter cache (or discover them &amp; populate the cache)
              SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
  
              // Assign the provided values to these parameters based on parameter order
              AssignParameterValues(commandParameters, parameterValues);
  
              // Call the overload that takes an array of SqlParameters
              return ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
          }
          else 
          {
              // Otherwise we can just call the SP without params
              return ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
          }
      }
  
  
  /// &lt;summary&gt;
      /// Execute a SqlCommand (that returns a 1x1 resultset) against the database specified in the connection string 
      /// using the provided parameters.
      /// &lt;/summary&gt;
      /// &lt;remarks&gt;
      /// e.g.:  
      ///  int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
      /// &lt;/remarks&gt;
      /// &lt;param name="connectionString"&gt;A valid connection string for a SqlConnection&lt;/param&gt;
      /// &lt;param name="commandType"&gt;The CommandType (stored procedure, text, etc.)&lt;/param&gt;
      /// &lt;param name="commandText"&gt;The stored procedure name or T-SQL command&lt;/param&gt;
      /// &lt;param name="commandParameters"&gt;An array of SqlParamters used to execute the command&lt;/param&gt;
      /// &lt;returns&gt;An object containing the value in the 1x1 resultset generated by the command&lt;/returns&gt;
      public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
      {
          if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
          // Create &amp; open a SqlConnection, and dispose of it after we are done
          using (SqlConnection connection = new SqlConnection(connectionString))
          {
              connection.Open();
  
              // Call the overload that takes a connection in place of the connection string
              return ExecuteScalar(connection, commandType, commandText, commandParameters);
          }
      }
  </code></pre>