Microsoft SQL Server 2012 Bible

Using SQL Synonyms

Views are sometimes employed to hide cryptic database schema names. Synonyms are similar to views, but they are more limited. Whereas views can project columns, assign column aliases, and build data using joins and subqueries, synonyms can assign only alternative names to tables, views, and stored procedures.

Use synonyms primarily to simplify complex object names, particularly with lengthy schema names. A synonym can change HumanResources.vEmployeeDepartmentHistory into EmpHist. Which would you rather type 100 times? The following script illustrates how to create the EmpHist synonym:

CREATE SYNONYM EmpHist
FOR HumanResources.vEmployeeDepartmentHistory;

Synonyms are part of the SQL standard and are used frequently by Oracle DBAs. Oracle includes both private and public synonyms. SQL Server synonyms are only public. Even though they were introduced to SQL Server with version 2005, I've seen little acceptance or use of synonyms in the SQL community.

Schemas enhance security and help prevent SQL injection attacks.

The hacker needs to guess the schema name as well as the table name. Little Bobby Tables (a standard DBA joke: http://xkcd.com/327/ ) would need to know myschema.students. Giving the table myschema.students an easy-to-guess synonym would defeat the purpose of using the schema as a mechanism to prevent SQL injection.

You can manage synonyms using Object Explorer, or CREATE and DROP DDL commands.