This topic has not yet been rated - Rate this topic

sp_changereplicationserverpasswords (Transact-SQL)

SQL Server 2005

Changes stored passwords for the Microsoft Windows account or Microsoft SQL Server login used by replication agents when connecting to servers in a replication topology. You would normally have to change a password for each individual agent running at a server, even if they all use the same login or account. This stored procedure enables you to change the password for all instances of a given SQL Server Login or Windows account used by all replication agents that run at a server. This stored procedure is executed at any server in the replication topology on the master database.

Topic link icon Transact-SQL Syntax Conventions

Syntax


sp_changereplicationserverpasswords [ @login_type = ] login_type
        , [ @login = ] 'login' 
        , [ @password = ] 'password'
    [ , [ @server = ] 'server' ]

Arguments

[ @login_type = ] login_type

Is the type of authentication for the supplied credentials. login_type is tinyint, with no default.

1 = Windows Integrated Authentication

0 = SQL Server Authentication

[ @login = ] 'login': Is the name of the Windows account or SQL Server login being changed. login is nvarchar(257), with no default

[ @password = ] 'password'

Is the new password to be stored for the specified login. password is sysname, with no default.

Note:
After changing a replication password, you must stop and restart each agent that uses the password before the change takes effect for that agent.

[ @server = ] 'server'

Is the server connection for which the stored password is being changed. server is sysname, and can be one of these values:

Value	Description
distributor	All agent connections to the Distributor.
publisher	All agent connections to the Publisher.
subscriber	All agent connections to the Subscriber.
% (default)	All agent connections to all servers in a replication topology.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_changereplicationserverpasswords is used with all types of replication.

Permissions

Only members of the sysadmin fixed server role can execute sp_changereplicationserverpasswords.

Other Resources

How to: View and Modify Replication Security Settings (Replication Transact-SQL Programming)

Help and Information

Getting SQL Server 2005 Assistance

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD