ALTER SERVICE (Transact-SQL)
Applies to: SQL Server Azure SQL Managed Instance
Changes an existing service.
Transact-SQL syntax conventions
ALTER SERVICE service_name
[ ON QUEUE [ schema_name . ]queue_name ]
[ ( < opt_arg > [ , ...n ] ) ]
[ ; ]
<opt_arg> ::=
ADD CONTRACT contract_name | DROP CONTRACT contract_name
service_name
Is the name of the service to change. Server, database, and schema names cannot be specified.
ON QUEUE [ schema_name. ] queue_name
Specifies the new queue for this service. Service Broker moves all messages for this service from the current queue to the new queue.
ADD CONTRACT contract_name
Specifies a contract to add to the contract set exposed by this service.
DROP CONTRACT contract_name
Specifies a contract to delete from the contract set exposed by this service. Service Broker sends an error message on any existing conversations with this service that use this contract.
When the ALTER SERVICE statement deletes a contract from a service, the service can no longer be a target for conversations that use that contract. Therefore, Service Broker does not allow new conversations to the service on that contract. Existing conversations that use the contract are unaffected.
To alter the AUTHORIZATION for a service, use the ALTER AUTHORIZATION statement.
Permission for altering a service defaults to the owner of the service, members of the db_ddladmin or db_owner fixed database roles, and members of the sysadmin fixed server role.
The following example changes the //Adventure-Works.com/Expenses
service to use the queue NewQueue
.
ALTER SERVICE [//Adventure-Works.com/Expenses]
ON QUEUE NewQueue ;
The following example changes the //Adventure-Works.com/Expenses
service to allow dialogs on the contract //Adventure-Works.com/Expenses
.
ALTER SERVICE [//Adventure-Works.com/Expenses]
(ADD CONTRACT [//Adventure-Works.com/Expenses/ExpenseSubmission]) ;
The following example changes the //Adventure-Works.com/Expenses
service to allow dialogs on the contract //Adventure-Works.com/Expenses/ExpenseProcessing
and to disallow dialogs on the contract //Adventure-Works.com/Expenses/ExpenseSubmission
.
ALTER SERVICE [//Adventure-Works.com/Expenses]
(ADD CONTRACT [//Adventure-Works.com/Expenses/ExpenseProcessing],
DROP CONTRACT [//Adventure-Works.com/Expenses/ExpenseSubmission]) ;
The following example changes the owner of //Adventure-Works.com/Expenses
to the dbo user.
ALTER AUTHORIZATION ON SERVICE::[//Adventure-Works.com/Expenses] TO dbo ;
GO