#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!!!
