how to script out proxy accounts?

Question

I have servers where I use proxy accounts to run ETL processes or even to backup SSAS databases using powershell through a job.

I can see what AD Accounts are related to my proxies:

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 

I can script out roles and logins and even table types but now I need to script out my proxies.

QUESTION: How to script out proxies?

I Want to script out the following:

I have got this so far:

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

Answer 1

DBATools has a convenient command Copy-DbaAgentProxy in its PoSh module that I highly recommend.

By default, all proxy accounts are copied. The -ProxyAccounts parameter is auto-populated for command-line completion and can be used to copy only specific proxy accounts.

If the associated credential for the account does not exist on the destination, it will be skipped. If the proxy account already exists on the destination, it will be skipped unless -Force is used.

Or, to generate the files, you can also use Get-DbaAgentProxy and Export-DbaScript.