Synonyms in SQL Server: Serving as Aliases for Database Objects

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

In SQL Server, a synonym acts as an alternative name for another database object. Think of it as an alias or a nickname. This base object can be located either on the local server or a remote server. Synonyms in SQL Server are primarily used to:

Provide a different, often simpler or more user-friendly name for a database object.
Create an abstraction layer, shielding client applications from the complexities of object locations or name changes.

Let’s illustrate this with an example. Imagine you have an Employee table within the Adventure Works database, residing on a server named Server1. Without synonyms, referencing this table from another server, say Server2, would require using the cumbersome four-part name: Server1.AdventureWorks.Person.Employee. Furthermore, if the Employee table were moved to a different server, all client applications using this four-part name would need to be updated.

Synonyms offer a solution to these challenges. On Server2, you could create a synonym called EmpTable within a schema named RemoteObjects. This synonym would point to the Employee table on Server1. Now, client applications on Server2 can simply use the two-part name RemoteObjects.EmpTable to access the Employee table on Server1. If the Employee table’s location changes, you only need to update the synonym EmpTable to reflect the new location. It’s important to note that modifying a synonym requires dropping and recreating it, as there’s no ALTER SYNONYM statement.

Synonyms are schema-bound objects, meaning they belong to a specific schema. Like other schema objects, synonym names must be unique within their schema. You can create synonyms for various types of database objects, including:

Functions:
- Assembly (CLR) stored procedures
- Assembly (CLR) scalar functions
- Replication-filter procedures
- SQL scalar functions
- SQL inline-tabled-valued functions
- Assembly (CLR) table-valued functions
- Assembly (CLR) aggregate functions
- SQL table-valued functions
- SQL stored procedures
Tables and Views:
- Tables* (User-defined, including local and global temporary tables)
- Views

*Includes local and global temporary tables

It’s worth noting that four-part names are not supported for function base objects. Also, a synonym cannot be based on another synonym, and user-defined aggregate functions cannot be referenced by synonyms.

The link between a synonym and its base object is purely name-based. Checks for the base object’s existence, type, and permissions are postponed until runtime. This means the base object can be altered, dropped, or even replaced by another object with the same name. For example, if a synonym dbo.MyContacts points to the Person.Contact table and the Contact table is replaced by a view named Person.Contact, MyContacts will then reference the view.

Synonym references are not schema-bound, allowing synonyms to be dropped at any time. However, dropping a synonym can lead to “dangling references,” which are only detected at runtime.

Synonyms and Schemas

When creating synonyms, schema considerations are important, especially if you are using a default schema you don’t own. In such cases, you must explicitly qualify the synonym name with a schema you do own. For instance, if your default schema is S2, but you own schema S1, you must prefix the synonym name with S1 when using the CREATE SYNONYM statement. Refer to CREATE SYNONYM (Transact-SQL) for detailed syntax and options.

Granting Permissions on Synonyms

Permission management for synonyms is straightforward. Only synonym owners, members of the db_owner database role, or members of the db_ddladmin database role have the authority to grant permissions on a synonym.

You can grant, deny, and revoke various permissions on synonyms, including:

CONTROL: Grants full control over the synonym.
EXECUTE: Allows users to execute a synonym that points to a stored procedure or function.
SELECT: Permits users to select data from a synonym referencing a table or view.
UPDATE: Allows users to update data through a synonym pointing to a table or view.
DELETE: Permits users to delete data via a synonym referencing a table or view.
INSERT: Allows users to insert data through a synonym pointing to a table or view.
TAKE OWNERSHIP: Enables users to take ownership of the synonym.
VIEW DEFINITION: Allows users to view the definition of the synonym.

Utilizing Synonyms

Synonyms can be seamlessly used in place of their base objects in numerous SQL statements and expression contexts. Here are some common examples:

Data Manipulation Statements:
- SELECT
- INSERT
- UPDATE
- DELETE
Execution Statements:
- EXECUTE
Subqueries:
- Sub-selects

When you interact with a synonym in these contexts, the action directly affects the underlying base object. For example, inserting data into a synonym that points to a table will actually insert the row into the referenced table.

However, there are limitations. You cannot reference synonyms located on linked servers.

While you can use a synonym as a parameter for the OBJECT_ID function, it’s important to note that the function will return the object ID of the synonym itself, not the base object.

Synonyms cannot be used in Data Definition Language (DDL) statements. For example, the following statements, attempting to alter a synonym named dbo.MyProduct, will result in errors:

ALTER TABLE dbo.MyProduct ADD NewFlag int null;
EXEC ('ALTER TABLE dbo.MyProduct ADD NewFlag int null');

Permission-related statements like GRANT, REVOKE, and DENY apply to the synonym itself, not the base object.

Furthermore, due to their non-schema-bound nature, synonyms cannot be referenced in schema-bound expression contexts such as:

CHECK constraints
Default expressions
Schema-bound views
Computed columns
Rule expressions
Schema-bound functions

For more in-depth information about schema-bound functions, consult Create User-defined Functions (Database Engine).

Retrieving Information About Synonyms

The sys.synonyms catalog view provides valuable metadata about synonyms within a database. It contains an entry for each synonym, exposing properties like the synonym’s name and the name of its base object. For more details, refer to sys.synonyms (Transact-SQL).

Extended properties can be used to add descriptive text, input masks, formatting rules, and other properties to synonyms. These properties are stored in the database, ensuring consistent interpretation across applications. For more information, see sp_addextendedproperty (Transact-SQL).

To determine the base type of a synonym’s base object, you can use the OBJECTPROPERTYEX function. See OBJECTPROPERTYEX (Transact-SQL) for further details.

Examples

The following example demonstrates how to retrieve the base type of a synonym’s base object when it’s a local object:

USE tempdb;
GO
CREATE SCHEMA SynSchema
GO
CREATE SYNONYM SynSchema.MyEmployee FOR AdventureWorks2022.HumanResources.Employee;
GO
SELECT OBJECTPROPERTYEX(OBJECT_ID('SynSchema.MyEmployee'), 'BaseType') AS BaseType;

This example shows how to get the base type for a synonym whose base object is remote, located on a server named Server1:

EXECUTE sp_addlinkedserver Server1;
GO
CREATE SYNONYM SynSchema.MyRemoteEmployee FOR Server1.AdventureWorks2022.HumanResources.Employee;
GO
SELECT OBJECTPROPERTYEX(OBJECT_ID('MyRemoteEmployee'), 'BaseType') AS BaseType;
GO