Sql Server Synonyms

According to Microsoft, A synonym is a database object that serves the following purposes.

• Provides an alternative name for another database object, referred to as the base object, that can exist on a local or remote server.
• Provides a layer of abstraction that protects a client application from changes made to the name or location of the base object.

For example, consider the Employee table of Adventure Works, located on a server named Server1. To reference this table from another server, Server2, a client application would have to use the four-part name Server1.AdventureWorks.Person.Employee. Also, if the location of the table were to change, for example, to another server, the client application would have to be modified to reflect that change.

To address both these issues, you can create a synonym, EmpTable, on Server2 for the Employee table on Server1. Now, the client application only has to use the single-part name, EmpTable, to reference the Employee table. Also, if the location of the Employee table changes, you will have to modify the synonym, EmpTable, to point to the new location of the Employee table.

Note : There is no ALTER SYNONYM statement, you first have to drop the synonym, and then re-create the synonym with the same name with the new changes.

In General words,A synonyms is a single-part name which can replace multi part name in SQL Statement. Use of synonyms cuts down typing long multi part server name and can replace it with one synonyms. It also provides an abstractions layer which will protect SQL statement using synonyms from changes in underlying objects (tables etc).

Synonyms can be created on the following objects only.

1. Assembly (CLR) Stored Procedure
2. Assembly (CLR) Table-valued Function
3. Assembly (CLR) Scalar Function
4. Assembly Aggregate (CLR) Aggregate Functions
5. Replication-filter-procedure
6. Extended Stored Procedure
7. SQL Scalar Function
8. SQL Table-valued Function
9. SQL Inline-table-valued Function
10. SQL Stored Procedure
11. View
12. Table (User-defined)  (Includes local and global temporary tables)

Create Synonyms :

USE AdventureWorks;

GO

CREATE SYNONYM MyProduct

FOR AdventureWorks2012.Production.Product;

GO

Use Synonyms :

USE AdventureWorks;

GO

SELECT TOP 5 *

FROM MyProduct;

GO

Drop Synonyms :

USE AdventureWorks;

GO

DROP SYNONYM MyProduct;

GO

SYNONYMS can be used only to change data of object not the schema of the object. SYNONYMS can be used with only SELECT, UPDATE, INSERT, DELETE, EXECUTE commands.

You can GRANT, DENY, REVOKE all or any of the following permissions on a synonym:

1. CONTROL
2. DELETE
3. EXECUTE
4. INSERT
5. SELECT
6. TAKE OWNERSHIP
7. UPDATE
8. VIEW DEFINITION

Reference:

http://msdn.microsoft.com/en-us/library/ms191230(v=SQL.90).aspx

http://msdn.microsoft.com/en-IN/library/ms177544.aspx

Gopikrishna