You can create a synonym in SQL Server that
references a base object, and then reference the synonym to perform
operations on the base object. You can use a synonym in place of a
threeor four-part naming convention. You can also use a synonym to
reference objects on a linked server, thereby masking the server name.
If the database on the linked server is moved, all you need to do is
change the synonym instead of having to change every object where the
linked server is referenced.
Following is a list of rules you need to know when using synonyms:
You can use a synonym to reference the base object in the following query contexts:
You cannot reference a synonym that is located on a linked server.
You cannot use a synonym in a Data Definition Language (DDL) statement, such as an ALTER TABLE command.
Synonyms are not schema bound; therefore, they cannot be referenced by schemabound objects such as these:
To create a synonym,
right-click on the Synonyms folder located under the database where you
would like to create the synonym and select New Synonym from the context
menu. This will bring up the New Synonym dialog box, as shown in Figure 1.
Next, fill in the fields and
choose an object type from the drop-down list. Following are the
components of a synonym definition:
Synonym Name is the name of the synonym that will be created.
Synonym Schema is the name of the schema where the synonym will be created.
Server Name is either the local server name or the name of a linked server containing the base object.
Database Name is the name of the database containing the base object.
Schema is the name of the schema where the base object is located.
Object Type defines the base object type (view, table, stored procedure, or function).
Object type is a drop-down selection in Figure 1.
Object Name is the name of the base object.
Figure 1 creates a synonym called EmpAddress
that references the HumanResources. EmployeeAddress table in the
AdventureWorks database on a linked server called KEN-PC. The results of
querying the EmpAddress synonym can be seen in Figure 2.
You can also use T-SQL to create a synonym. The following statement is the equivalent to the parameters provided to create the EmpAddress synonym back in Figure 9-10.
CREATE SYNONYM [dbo].[EmpAddress]
FOR [KEN-PC].[AdventureWorks].[HumanResources].[EmployeeAddress]
You do not need
permissions on the base object in order to create a synonym. For that
matter, the base object does not even need to exist to create a synonym;
all of the security and existence checks are deferred until runtime.
Also, the permissions that you set on a synonym apply only to the
synonym and not to the base object. For example, when you grant Select
permission to a synonym, you are giving permission to query the synonym
itself, and not to query the underlying object. You can query the sys.synonyms catalog view to return the metadata about the synonym, including the base object name.
To drop a synonym, all you need to do is execute the DROP SYNONYM command followed by the synonym name or right-click the synonym and select Delete from the context menu.
DROP SYNONYM [schema].SynonymName
You can drop a synonym even
if other objects are referencing it. You will not encounter an error
until executing the object that references the synonym.