Misleading SQLServerException: Parameter was not defined for stored procedure #608
Comments
|
Maybe sp_helprotect (https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-helprotect-transact-sql) could be used beforehand to check if permission is granted? |
|
Here's some repro code in plain JDBC: package main.java;
import com.microsoft.sqlserver.jdbc.SQLServerException;
import org.junit.Assert;
import org.junit.Rule;
import org.junit.Test;
import org.junit.rules.ExpectedException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class MssqlJdbc608 {
private static final String URL = "jdbc:sqlserver://<INSERT_URL_HERE>;databaseName=test_sp";
@Rule
public ExpectedException expectedEx = ExpectedException.none();
@Test
public void testPositionalParamPermissionDenied() throws SQLException {
expectedEx.expect(SQLServerException.class);
expectedEx.expectMessage("The EXECUTE permission was denied on the object 'test'");
try (Connection conn = DriverManager.getConnection(URL, "reader", "password_reader")) {
CallableStatement stmt = conn.prepareCall("{ call test(?) }");
stmt.setInt(1,42);
stmt.execute();
}
}
//fails with 'Parameter id was not defined for stored procedure test.'
@Test
public void testNamedParamPermissionDenied() throws SQLException {
expectedEx.expect(SQLServerException.class);
expectedEx.expectMessage("The EXECUTE permission was denied on the object 'test'");
try (Connection conn = DriverManager.getConnection(URL, "reader", "password_reader")) {
CallableStatement stmt = conn.prepareCall("{ call test(?) }");
stmt.setInt("id", 42);
stmt.execute();
}
}
@Test
public void testNamedParamPermissionGranted() throws SQLException {
try (Connection conn = DriverManager.getConnection(URL, "executor", "password_executor")) {
CallableStatement stmt = conn.prepareCall("{ call test(?) }");
stmt.setInt("id", 42);
stmt.execute();
}
}
public static boolean isPermissionGranted(Connection conn, String sprocName) throws SQLException {
CallableStatement stmt = conn.prepareCall("{ call sp_helprotect(?) }");
stmt.setString(1, sprocName);
try {
stmt.execute();
}catch(SQLServerException ex) {
return false;
}
return true;
}
@Test
public void testHelprotectPermissionDenied() throws SQLException {
try (Connection conn = DriverManager.getConnection(URL, "reader", "password_reader")) {
Assert.assertFalse("Permission should not be granted", isPermissionGranted(conn, "test"));
}
}
@Test
public void testHelprotectPermissionGranted() throws SQLException {
try (Connection conn = DriverManager.getConnection(URL, "executor", "password_executor")) {
Assert.assertTrue("Permission should be granted", isPermissionGranted(conn, "test"));
}
}
} |
|
Hi @cnsgithub, thanks for your contribution and the detailed explanation. We have been able to reproduce the issue locally, and can confirm the behavior of the driver is not expected. A proposed change is under review. In the mean time, you can test the changes by pulling this branch. Please feel free to leave a comment regarding any concerns or issues you come across. |
|
Hi @rene-ye, I just tested your proposed fix and can confirm that everything works as expected now. Good work, thanks! |
|
PR #635 has been merged and can be expected in the 6.5.0 release. Closing issue. |
|
For which version this issue is fixed as of now . We are using 7.2.2.jre8 with RHEL OS and Open JDK facing same issue. |
|
Using spring boot and sql server I got the following error: And I had my application.properties configured as follows: Solution: |
Driver version or jar name
mssql-jdbc:6.3.6.jre8-previous (also tested with latest stable release)
SQL Server version
SQL Server 2017 (also tested with 2016)
Client operating system
Windows 8.1 (also tested with Windows Server 2012 R2)
Java/JVM version
Oracle 9.0.4 (also tested with Java 8)
Table schema
n/a
Problem description
When trying to execute a simple stored procedure providing one incoming parameter the driver gave me:
This behavior seemed to me very weird since the same code has worked on production site for months - until I finally detected different object permissions of the executing user. The exception arises just in case of missing execute permission on the stored procedure and can therefore be simply fixed by just granting that permission. However, the exception is somewhat misleading, it should be changed to a more appropriate, meaningful one.
Although, the code producing the above stack trace is based on JPA/Hibernate, you should be able to reproduce it with plain JDBC as well - or even in SQL directly (see repro code below).
Entity (just for declaring the named stored procedure):
Main method for testing:
I think the misleading exception arises because SQLServerCallableStatement.findColumn("id") retrieves zero results from sp_proc_columns and assumes that there is no column named 'id' in the stored procedure. This happens before actually trying to execute the user stored procedure which would probably lead to a meaningful 'The EXECUTE permission was denied' message.
Expected behavior and actual behavior
Expected: 'The EXECUTE permission was denied'
Actual: 'Parameter was not defined for stored procedure'
Repro code
The text was updated successfully, but these errors were encountered: