The Configure MS SQL Server Connection window is displayed to set up the connection string for a Microsoft SQL Server.

You can configure a Microsoft SQL Server connection that connects to a table, view or a stored procedure, or a synonym.

In order to use a synonym, the following permissions are needed:

SQL Permissions

The user or the role assigned must have permissions to set the underlying object from the synonym.

• If the object is a table or view, the "Select" permissions is required.

• If the object is a stored procedure, the "Execute" permissions is required.

Limits

Synonyms have the following limitations:

• Synonyms are supported for the following objects only:

  • Table

  • View

  • Stored Procedure

SQL Commands

The user running the command requires appropriate privileges to perform operations.

• For a synonym on a table or view:

  • Create synonym.

    USE [MyDatabase]
    GO
    CREATE SYNONYM [MySchema].[MySynonym] FOR [SourceDatabase].[SchemaName].[TableOrViewName]
    GO
    

  • Granting "Select" privilege.

    USE [SourceDatabase]
    GO
    GRANT SELECT ON [SchemaName].[TableOrViewName] TO [UserOrRoleName]
    GO
    

• For synonym on a stored procedure:

  • Create synonym.

    USE [MyDatabase]
    GO
    CREATE SYNONYM [MySchema].[MySynonym] FOR [SourceDatabase].[SchemaName].[StoreProcedureName]
    GO
    

  • Granting "Select" privilege.

    USE [SourceDatabase]
    GO
    GRANT EXECUTE ON [SchemaName].[StoreProcedureName] TO [UserOrRoleName]
    GO
    

You can configure an Microsoft SQL Server database connection by following these steps: