Sql-Server
如何編寫代理帳戶?
我有伺服器,我使用代理帳戶執行 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-DbaAgentProxy
DBATools在其 PoSh 模組中有一個我強烈推薦的便捷命令。預設情況下,會複製所有代理帳戶。-ProxyAccounts 參數為命令行完成自動填充,可用於僅複製特定代理帳戶。
如果目標上不存在該帳戶的關聯憑據,則將跳過該憑據。如果目標上已經存在代理帳戶,除非使用 -Force,否則將跳過它。
或者,要生成文件,您還可以使用
Get-DbaAgentProxy
和Export-DbaScript
。