Credentials and Proxy creation Script in SQL Server 2008R2 or How to access subsystems by SQl Server ?

#How to create credentials and proxy account by script in SQL2008R2#

Script Uses: In SSIS package Migration and SQL server agent job to access outside file. 

Credentials are used for to allow sql server authenticated logins to access file source or any action outside sql server.

#Scripts for Credentials creation#

USE [master]

GO

-- Update the SECRET (password) in CREATE CREDENTIAL query before running script

IF NOT EXISTS (SELECT * FROM sys.credentials WHERE name = 'DBA_Report')

BEGIN

CREATE CREDENTIAL [DBA_Report] WITH IDENTITY = N'prod\$sa-ddh', SECRET ='sssss'

END

GO

-->Windows login name:prod\$sa-ddh

-->Secret: specify the password

#Create a proxy account named ‘DBA_Report’to map the credentials ‘DBA_Report’#

-- Drop the proxy if it already exists

IF EXISTS (SELECT 1 FROM msdb.dbo.sysproxies WHERE name = 'DBA_Report')

BEGIN

  EXEC msdb.dbo.sp_delete_proxy @proxy_name = N'DBA_Report'

END

GO

-- Add the proxy

EXEC msdb.dbo.sp_add_proxy @proxy_name=N'DBA_Report',@credential_name=N'DBA_Report',

        @enabled=1

GO

-- Grant proxy account to SQL Server Agent Sub-systems(subsystem_id=11) into SSIS PAckage Execution folder in Proxies.

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'DBA_Report', @subsystem_id=11

  

Hope you are happy in accessing sub systems like SSIS, Command shell and active x script!!!