Scenario A:
SqlConnection con = new SqlConnection(myConnString);
SqlDataAdapter adp = new SqlDataAdapter("EXEC spGetUserInfo 42", con);
DataSet ds;
adp.Fill(ds);
Scenario B:
SqlConnection con = new SqlConnection(myConnString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "spGetUserInfo";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserID", 42));
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet ds;
adp.Fill(ds);
Question
In a discussion of how to strengthen SqlServer from sql injection attacks separately from modifying the code of the main application that calls the database, the question was asked if SqlServer can be configured to simply not execute stored procs written as script A, only allowing execution requests written in the form of scenario B. The theory is that scenario A would be vulnerable to injection if the host application did not perform input validation, allowing something like "EXEC spGetUserInfo 42; drop database foo; -" can be executed, while scenario B just can’t fulfill where SqlServer cannot convert "42; drop database foo; -" to an integer.
SqlServer, procs, Scenario A?