The connect to Command

The connect to command enables users to specify the server to which a passthrough connection is required.

The syntax of the command is:

connect to server_name

where server_name is the name of a server added to the sysservers table, with its server class and network name defined. See sp_addserver in the Reference Manual: Procedures.

When establishing a connection to server_name on behalf of the user, the server uses:

A remote login alias set using sp_addexternlogin, or
The name and password used to communicate with the SAP ASE.

In either case, if the connection cannot be made to the server specified, the reason is contained in a message returned to the user.

Once a passthrough connection has been made, the Transact-SQL parser and compiler are bypassed when subsequent language text is received. Any statements received by the server are passed directly to the specified remote server.

Note: Some database management systems do not recognize more than one statement at a time and produce syntax errors if, for example, multiple select statements were received as part of a single language text buffer.

After statements are passed to the requested server, any results are converted into a form that can be recognized by the Open Client interface and sent back to the client program.

To exit from passthrough mode, issue the disconnect or disc, command. Subsequent language text from this client is then processed using the Transact-SQL parser and compiler.

Permission to use connect to must be explicitly granted by the system administrator. The syntax is:

grant connect to user_name

To revoke permission to use connect to, the syntax is:

revoke connect from user_name

The connect to permissions are stored in the master database. To globally grant or revoke permissions to “public”, the system administrator sets the permissions in the master database; the effect is server-wide, regardless of what database is being used. The system administrator can only grant or revoke permissions to or from a user if the user is a valid user of the master database.

The system administrator can grant or revoke “all” permissions to or from “public” within any database. If the permissions are in the master database, “all” includes the connect to command. If they are in another database, “all” does not include the connect to command.

In this example, the system administrator wants to revoke permission from “public” and wants only the user “fred” to be able to execute the connect to command. “fred” must be made a valid user of master. To do this, the system administrator issues the following commands in master:

revoke connect from public
 sp_adduser fred
 grant connect to fred