3

I am trying to set an application role through a JDBC connection using a prepareCall. It seems to work ok (i.e. syntax wise) but SQL server 2008 returns this error:

Application roles can only be activated at the ad hoc level

I am not firing this from within a stored procedure or anything, just directly from my JDBC connection, as such:

CallableStatement cstmt = con.prepareCall("{call sys.sp_setapprole(?, ?, ?, ?)}");
//setup all the IN and OUT parameters here
cstmt.execute();

Any ideas for why this doesn't work?

j0k
  • 22,600
  • 28
  • 79
  • 90
Ayyoudy
  • 3,641
  • 11
  • 47
  • 65

2 Answers2

3

Use the following code to execute sp_setapprole, it wors for me!

// substitute userName and password with your own code
try {
  String sql = "EXEC sp_setapprole '" + userName + "', '" + password + "'";
  Statement st = con.createStatement();
  st.execute(sql);
} catch (Exception ex) {
  ex.printStackTrace();
}
passby
  • 31
  • 2
  • How do you retrieve the OUTPUT parameter this way? – Simon Arsenault Nov 27 '13 at 15:13
  • You can execute multiple statements in a query, so you can do `con.createStatement().executeQuery(sql).next().getBytes(1)` where sql is: `DECLARE @gotcha AS VARBINARY(8000); EXEC sp_setapprole 'user', 'pass', true @gotcha; SELECT @gotcha;` !! Hacky (and awkward, using a binary literal for `sp_unsetapprole` later), but ... works!. – David Bullock Jan 06 '21 at 08:32
3

As it turns out the JDBC driver I was using, Microsoft's JDBC driver, has no way of turning off Prepared Statements or Statement Pooling. So everything that was sent to the database was wrapped in a sp_prepexec() which the sys.sp_setapprole() detected and didn't like as it can't be wrapped inside another procedure and must execute on its own directly on the database. The solution unfortunately is to go with another JDBC driver.

Ayyoudy
  • 3,641
  • 11
  • 47
  • 65