Share via

Предоставление временных прав с помощью SQL Server Agent Jobs

  • Article

???????? ???????????????? ???????? – ???????????? ?????? ????? ???????? ????????? ?????? ??? ??????; ? «?????????? ?????» ?? ?? ????? ???? ????????????? ??? ?????? ???????????????, ?? ? ?????? ??????, ????????, ??? ??????? (??????? ????? ??????????????? ?????), ? ???????, ????? ????? ??? ?????, ??? ? ??????…

??? ?????? ? ????? ???????? ?????????????? ??, ????????, ?? ????????????? ??????? ???????????? ????? ?? ???????? ?????? ? ?????????? ???? ?? ???????: «???????? ????? ???? ????????????? ??????? ?????». ?????? ??? ?????? – ??????? ????????, ???? ?? ???? ???????? ? ???????, ??????? ??? ?? ??????????.

???? ?????? ???? ???????????? ????????? ?????, ??????? ????????????? ????? ???????? ? ???????????? ?????.

????????, ? ??? ???? ???????????? Bob ? ????????? p_test, ??????? ??? ?????? ????? ?????????. ????????????? SQL Server Agent Jobs. ?? ????? ???????????? ????? ?? ?????????? ???? ????????? (GRANT EXECUTE ON p_test TO Bob) ? ???????? Job, ??????? ????? ??????????? ???? ??? ? ??????????? ?????, ? ????? ???????? ????? ???????????? ?? ??????????.

??????? Job ????? ????? UI SQL Server Management Studio (SQL Server Agent -> Jobs -> New Job…) ??? ??????????. ????????, ????????? ?????? ??????? Job ??? ?????? ????? ???????????? Bob ????????? ????????? p_test ? ????????????? ?? ?????????? (??????? Schedule) ???? ??? 10.06.2009 ? 23:59:59

DECLARE @jobId BINARY(16)

EXEC msdb.dbo.sp_add_job @job_name=N'RevokeTempPerm',

            @enabled=1,

            @notify_level_eventlog=0,

            @notify_level_email=0,

            @notify_level_netsend=0,

            @notify_level_page=0,

            @delete_level=0,

            @description=N'To revoke temporary permissions',

            @category_name=N'[Uncategorized (Local)]',

            @owner_login_name=N'sa', @job_id = @jobId OUTPUT

EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'RevokeTempPerm',

            @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'TSQL',

            @command=N'REVOKE EXECUTE ON p_test TO Bob',

            @database_name=N'db_source',

            @flags=0

EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'RevokeTempPerm',

            @enabled=1,

            @freq_type=1,

            @freq_interval=0,

            @freq_subday_type=0,

            @freq_subday_interval=0,

            @freq_relative_interval=0,

            @freq_recurrence_factor=0,

            @active_start_date=20090610,

            @active_end_date=99991231,

            @active_start_time=0,

            @active_end_time=235959,

            @schedule_uid=N'0882904e-9eaa-46ee-b8f2-ccfb56b88492'

EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

GO

?????????? ????? ????????? Job ????????????? ??????????????? ???????????? ????? ????????????? ? ???????????? ?????.

???????? ???????? ?????? ?????? ???????? ??, ??? ?? ???????? ????? Job's ????? ?????????????. ??????????????, ????? ????? ??????? ????????? ????????????? ????? ?? ??????? ? ????????? ???????????? Job, ???? ???????????????? ?? ????? ?????? ?? ?????????? (Schedule).

Comments