How can I change the database schema using a Statement.execute() in Java ( and SQL Server)?

Question

I need to execute a SQL Server system stored procedure, programmatically, and since it executes in the current schema, I need to change it on the fly.

Like this

Statement st = connection.createStatement(); st.execute("EXEC SP_ADDUSER ' ', ' '");

But SP_ADDUSER only executes on the current schema set for the connection, so if I wanted to create users in various schemas, I'd need to change it, and that's what I am looking for.

Answer 1

I don't believe it's possible to change which database a connection points to.

You'll probably need to create a separate DataSource/Connection for each database (schema).

Answer 2

EXEC <DatabaseName>..sp_adduser can be run from a connection to any database (even master, say). The connection will not be affected.

For instance, the following appears to work fine on my system:

USE master
EXEC sp_addlogin 'test1'
EXEC SandBox..sp_adduser 'test1'

The same things works fine through the client. Is your client connection altering your SQL?

using System;
using System.Data.SqlClient;

namespace TestUse
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection cn = new SqlConnection("Server=(local);Database=master;Trusted_Connection=True;");
            cn.Open();
            SqlCommand cmd = new SqlCommand("USE master; EXEC sp_addlogin 'test1'; EXEC SandBox..sp_adduser 'test1'", cn);
            cmd.ExecuteNonQuery();
            cn.Close();
        }
    }
}