CODEWITHSUNDEEP
c#sqlsql-serversql-server-2008permissions
Legend
Legend

Reputation: 117000

Queries work in debug mode but not in deployment mode?

I have a C# application and the following is an excerpt from my Web.config file:

  <connectionStrings>
    <add name="MyDB" connectionString="Data Source=MACHINENAME\\SQLEXPRESS;Initial Catalog=DBName;Integrated Security=True;MultipleActiveResultSets=true" providerName="System.Data.SqlClient"/>
  </connectionStrings>
  <system.web>
    <authentication mode="Windows" />
    <identity impersonate="false" />
  </system.web>

In the code itself, I have a query like this:

[WebMethod]
public string InsertField(string str1, string str2, string str3)
{

    string sql = @"
          UPDATE DBNAME.dbo.TableName SET Field2 = '{2}', Modified_At=GETDATE() WHERE Field1 = '{0}' AND Field2 = '{1}';

          IF @@ROWCOUNT = 0
              INSERT INTO DBNAME.dbo.TableName (Field1, Field2, Field3, Modified_At)
              VALUES('{0}', '{1}', '{2}', GETDATE())
            ";

    sql = String.Format(sql, str1, str2, str3);

    try
    {
          string ConString = Constants.connString;
          con = new SqlConnection(ConString);

          cmd = new SqlCommand(sql, con);
          con.Open();
          cmd.ExecuteNonQuery();
          return "Success";
    }
    catch (Exception x)
    {
          return x.Message;
          //Response.Write(x);
     }

}

My web application runs perfectly fine when I debug it but when I publish it, the queries fail. Debugging with Firebug yields the following error:

{
    "d": "The UPDATE permission was denied on the object "TableName", database "DBName", schema "dbo".\r\nThe INSERT permission was denied on the object "TableName", database "DBName", schema "dbo"."
}

Any suggestions on how I can fix this?

Upvotes: 0

Views: 1172

Answers (2)

TGH
TGH

Reputation: 39278

The user doesn't have sufficient privileges to perform the update in your prod environment. You have to grant update permission to the user you are running as

Upvotes: 1

Justin Pihony
Justin Pihony

Reputation: 67135

Yes, this is a pretty obvious error. You need to make sure that whatever account your web is setup to run as should have access to update/insert the given table.

As the user seems to not have been set up in the connection string. It will use whatever the web method is running as. So, find out what that account is and grant the appropriate perms. It worked in development because it was probably running under your account, which probably had full access to your dev database.

Upvotes: 2

Related Questions