특정 Linked Server 의 계정번경

use master

go

 

 

declare @srvName varchar(100)

 

declare curSrv cursor fast_forward for

                  SELECT

                  srv.name

                  -- ISNULL(ll.remote_name, N'') AS [RemoteUser],

                  -- CAST(ll.uses_self_credential AS bit) AS [Impersonate],

                  -- ll.modify_date AS [DateLastModified]

                  FROM sys.servers AS srv

                                   INNER JOIN sys.linked_logins ll ON ll.server_id=CAST(srv.server_id AS int)

                                   LEFT OUTER JOIN sys.server_principals sp ON ll.local_principal_id = sp.principal_id

                  WHERE ll.remote_name = 'Account_Name'  -- 기타 등등 조건 추가 (원하는 녀석으로)

                  group by srv.name

 

open curSrv

 

fetch next from curSrv

                  into @srvName

                 

while @@FETCH_STATUS = 0

begin

                 

                  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = @srvName, @locallogin = NULL , @useself = N'False',

                                   @rmtuser = N'NewUser', @rmtpassword = N'NewPassword'

                 

                  fetch next from curSrv

                                   into @srvName

end

 

close curSrv

deallocate curSrv