Synonyms in SQL Server

Wikipedia says that “Synonyms are different words with identical or very similar meanings like student and pupil is synonym, buy and purchase is synonym.”

And concept of “Synonym” in SQL Server is not different at all, as SQL Server synonym is alternate name of a database object. For example, you can define an alternate name usp_Get_Invoices for a stored procedure Sales.dbo.usp_Get_Current_Month_Invoices.

Few things we should know about synonums are following:

1.       Synonym can be used to refer an object in current server or remote server.

2.       Synonym can be created for following types of objects:

a.       Tables: User-defined that include local and global temporary tables also

b.      Stored Procedures: SQL, CLR and extended, all types of stored procedures

c.       Functions:  SQL and CLR both types of functions

d.      Views

3.       A synonym can not be the base object for another synonym.

4.       Dropping a synonym does not affect the base object.

5.       Synonyms are not schema bound to base object and remain deferred until run-time. In other words the base object’s existence, validity, and permissions are checked only at run time.

6.    Syntax to create a synonym is following:

       CREATE SYNONYM [schema_Name.]Synonym_Name FOR base_Object

7.    Syntax to drop a synonym is following:

       DROP SYNONYM [schema_Name.]Synonym_Name     

8.       Synonym can be used in DML statements. Let us test with following code:

CREATE TABLE dbo.tblTest (Col1 INT, Col2 INT)

GO

INSERT INTO dbo.tblTest VALUES(1,10)

GO

CREATE SYNONYM syn_Table FOR dbo.tblTest

GO

SELECT * FROM syn_Table                -- retunrs 1 record

GO

INSERT INTO syn_Table VALUES (1,1)

SELECT * FROM syn_Table                -- returns 2 records

9.       Synonym can not be used in DDL statements. If we execute the below statements:

ALTER TABLE syn_Table ADD Col3 INT

Following error occur:

Msg 4909, Level 16, State 1, Line 1

Cannot alter 'syn_Table' because it is not a table.

8.       Synonyms are like view in permission checking and ownership chaining. For example if an user TestUser does not have execute permission on a stored procedure dbo.proc_TestSynonym, but have execute permission on a synonym of this stored procedure dbo.syn_Proc then user can execute dbo.syn_Proc successfully. Test this using below script:

CREATE PROCEDURE dbo.proc_TestSynonym

AS

SELECT * FROM dbo.tblTest        --change the table name

GO

CREATE LOGIN TestUser WITH PASSWORD = 'TestUser', CHECK_POLICY = OFF

GO

USE Test    --DB name

GO

CREATE USER TestUser

GO

DENY EXECUTE ON dbo.proc_TestSynonym TO TestUser 

GO 

CREATE SYNONYM dbo.syn_Proc FOR dbo.proc_TestSynonym 

GO 

GRANT EXECUTE ON dbo.syn_Proc TO TestUser 

GO 

EXEC AS USER = 'TestUser' 

GO 

PRINT '1' 

EXEC dbo.proc_TestSynonym --fail with permission denied error

GO 

PRINT '2' 

EXEC dbo.syn_Proc    --successfully executes 

GO 

REVERT 

GO 

Use the below scrip to undo the changes performed by above script:

DROP SYNONYM dbo.syn_Proc

GO

DROP PROCEDURE dbo.proc_TestSynonym

GO

DROP USER TestUser

GO

DROP LOGIN TestUser