Search code examples
sqlsql-servermssql-jdbc

Alias/Synonym for a database based on user

Is it possible to have the same query, but based on different user currently logged in, request the info from different databases?

An example: we have an application with different environments dev/test/int etc. For each environment, we have different JDBC connections to the MSSQL Server. The main idea is, to have always the same query like:

SELECT * FROM config_db.clients

But internally, based on current user in the connection string (like user_dev, user_test and so on) get the response from config_db_dev, config_db_test etc.? I hope the question makes sense.

Thanks in advance!

Solution

  • Is it possible to have the same query, but based on different user currently logged in, request the info from different databases?

    Sure. Logins have a default database and users have a default schema.

    Both of these can affect object name resolution.

    if you use a 1-part name, like

    SELECT * FROM clients

    then SQL Server will look for an object named "clients" in the user's default schema first, then in the dbo schema in the current database.

    If you use a 2-part name, like

    SELECT * FROM foo.clients

    then SQL Server will look for an object named "clients" in in the "foo" schema in the current database.