What is SQL Server Synonym and How To Use It

Synonym is to give alias (an alternative name) to some objects on a database basis. The synonym can be created for following objects.

  • Table
  • View
  • Stored Procedure
  • Function

After creating Synonym, you can access the original object via synonym.

So why do we need synonym?

Consider that there are many different code written in the form of Server1.Database.Schema.Table to access a table in the application.

If you move the database from Server1 to Server2 or move the table to another schema, this code block will not work.

If schemas or servers of objects(such as Table, View, Stored Procedure, and Function) change, you can only resolve the problem without modifying the code by deleting synonym and recreating it in the same name to point to the new location of the table.

Create Synonym:

Right click on Synonyms under the database and click New Synonym…

We then make the necessary choices as in the following table:

Synonym name: We give a name to the synonym
Synonym schema: We’re choosing the schema where we create Synonym.
ServerName: The Server where the object is located.
Database name: The Database where the object is located.
Schema: The Schema where the object is located.
Object type: Object Type(Tablo,View,Stored Procedure or Function)
Object name: We’re choosing the object

Then go to the Permissions tab, click Search and select a user previously maped to the database and give the necessary privileges to the user.

You can read my article “How To Create a Login On SQL Server(Manage Logins)” to learn how to map a Login to a database.

Once you’ve created Synonym, you can query it as follows.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *