Sql-Server

如何編寫代理帳戶?

  • April 15, 2020

我有伺服器,我使用代理帳戶執行 ETL 程序,甚至通過作業使用 powershell 備份 SSAS 數據庫

我可以看到哪些 AD 帳戶與我的代理相關

use msdb
go

select 
   s.job_id
   ,s.name as [job_name]
   ,js.step_id
   ,js.step_name   
   ,js.subsystem
   ,js.proxy_id
   ,js.command
   , c.credential_identity
from sysjobs S
INNER JOIN sysjobsteps JS ON S.job_id = js.job_id
INNER JOIN sysproxies p ON js.proxy_id = p.proxy_id
INNER JOIN sys.credentials as c on c.credential_id = p.credential_id 

在此處輸入圖像描述

我可以編寫角色登錄名甚至表類型的腳本,但現在我需要編寫代理腳本。

問題: 如何編寫代理腳本?

我想編寫以下腳本:

在此處輸入圖像描述

到目前為止,我已經得到了這個:

IF OBJECT_ID('TEMPDB..#tmp_sp_help_proxy','U') IS NOT NULL
  DROP TABLE #tmp_sp_help_proxy

create table #tmp_sp_help_proxy(proxy_id int null, 
                              name nvarchar(128) null, 
                              credential_identity nvarchar(128) null, 
                              enabled tinyint null, 
                              description nvarchar(1024) null, 
                              user_sid varbinary(40) null,  
                              credential_id int null, 
                              credential_identity_exists int null)

insert into #tmp_sp_help_proxy(proxy_id, name, credential_identity, enabled, description, user_sid, credential_id, credential_identity_exists) 
exec msdb.dbo.sp_help_proxy



SELECT
tshp.name AS [Name],
tshp.proxy_id AS [ID],
CAST(tshp.enabled AS bit) AS [IsEnabled],
ISNULL(tshp.description,N'''') AS [Description],
tshp.credential_id AS [CredentialID],
ISNULL(sc.name,N'''') AS [CredentialName],
ISNULL(tshp.credential_identity,N'''') AS [CredentialIdentity]
FROM
#tmp_sp_help_proxy AS tshp
INNER JOIN sys.credentials AS sc ON sc.credential_id = tshp.credential_id

在此處輸入圖像描述

Copy-DbaAgentProxyDBATools在其 PoSh 模組中有一個我強烈推薦的便捷命令。

預設情況下,會複製所有代理帳戶。-ProxyAccounts 參數為命令行完成自動填充,可用於僅複製特定代理帳戶。

如果目標上不存在該帳戶的關聯憑據,則將跳過該憑據。如果目標上已經存在代理帳戶,除非使用 -Force,否則將跳過它。

或者,要生成文件,您還可以使用Get-DbaAgentProxyExport-DbaScript

引用自:https://dba.stackexchange.com/questions/264552