Sample Header Ad - 728x90

how to script out proxy accounts?

1 vote
1 answer
1300 views
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 enter image description here 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: enter image description here 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 enter image description here
Asked by Marcello Miorelli (17274 rep)
Apr 8, 2020, 07:01 AM
Last activity: Apr 15, 2020, 07:55 PM