Changing Linked Server Passwords
How to article on changing passwords for linked servers via TSQL and SSMS.
2017-01-26
15,064 reads
set nocount on; declare @sqlcmds table ( srvname nvarchar(128), sqlcmd varchar(1024) ); -- declare @sqlcmd_concat varchar(max) = ''; -- insert into @sqlcmds select srvname, sqlcmd = 'select instance = (select name from ' + srvname + '.master.sys.servers where server_id = 0), linked_server = ''' + srvname + ''', dbnm = name from ' + srvname + '.msdb.sys.databases where name like ''%ODS%''' + ' and state_desc = ''ONLINE''' from master.dbo.sysservers where (srvname like '%[_]MASTER[_]%' or srvname like '%[_]SLAVE__[_]%'); -- select @sqlcmd_concat += case @sqlcmd_concat when '' then '' else ' union all ' end + sqlcmd from @sqlcmds; -- exec(@sqlcmd_concat);