讓一般 login 建立 job 時,可以採用 proxy

巧玫提的有趣的問題,答案是需要呼叫 dbo.sp_grant_login_to_proxy,簡單的練習如下:

create login test with password=’abc’, check_expiration=off,check_policy=off

create credential abc with identity=N’sql2014\a’, secret=N’a’

use msdb

create user test for login test

alter role SQLAgentUserRole add member test

GO
EXEC msdb.dbo.sp_add_proxy @proxy_name=N’abc’,@credential_name=N’abc’,
        @enabled=1
GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’abc’, @subsystem_id=3
GO

/*
訊息 14516,層級 16,狀態 1,程序 sp_verify_proxy_permissions,行 190
Proxy (2) 不允許用於子系統 "CmdExec" 與使用者 "test"。透過呼叫 sp_grant_proxy_to_subsystem 或 sp_grant_login_to_proxy 來授與權限。
*/
execute as user=’test’
    DECLARE @jobId BINARY(16)
    EXEC msdb.dbo.sp_add_job @job_name=N’test’, @job_id = @jobId OUTPUT

    EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’test’,
            @step_id=1,
            @cmdexec_success_code=0,
            @on_success_action=1,
            @on_success_step_id=0,
            @on_fail_action=2,
            @on_fail_step_id=0,
            @retry_attempts=0,
            @retry_interval=0,
            @os_run_priority=0, @subsystem=N’CmdExec’,
            @command=N’"C:\Program Files\Excel Refresher\refreshexcel" "\\sql2014\drop\活頁簿1.xlsx"’,
            @output_file_name=N’C:\logs\refresh.log’,
            @flags=2,
            @proxy_name=N’abc’
revert

exec msdb.dbo.sp_delete_job @job_name=N’test’, @delete_unused_schedule=1

–要賦予 login 可以使用 proxy
EXEC dbo.sp_grant_login_to_proxy
    @login_name = N’test’,
    @proxy_name = N’abc’ ;
GO

execute as user=’test’
    DECLARE @jobId BINARY(16)
    EXEC msdb.dbo.sp_add_job @job_name=N’test’, @job_id = @jobId OUTPUT

    EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’test’,
            @step_id=1,
            @cmdexec_success_code=0,
            @on_success_action=1,
            @on_success_step_id=0,
            @on_fail_action=2,
            @on_fail_step_id=0,
            @retry_attempts=0,
            @retry_interval=0,
            @os_run_priority=0, @subsystem=N’CmdExec’,
            @command=N’"C:\Program Files\Excel Refresher\refreshexcel" "\\sql2014\drop\活頁簿1.xlsx"’,
            @flags=2,
            @server=N’SQL2014′,
            @proxy_name=N’abc’

    –似乎不是 sysadmin 建立的 job 要多設定,否則會有以下的錯誤
    /*
    訊息 14256,層級 16,狀態 1,程序 sp_start_job,行 123
    無法啟動作業 "test" (識別碼 9BEF0503-F8A6-4F51-9596-0BAD743DFF34),因為未定義任何作業伺服器。請呼叫 sp_add_jobserver 建立作業和作業伺服器的關聯。    */
    EXEC dbo.sp_add_jobserver @job_name = N’test’ ;
revert

exec sp_start_job ‘test’

exec msdb.dbo.sp_delete_job @job_name=N’test’, @delete_unused_schedule=1
drop user test
exec sp_delete_proxy @proxy_name=’abc’
drop credential abc
drop login test

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: