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


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


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


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


????????, ? ??? ???? ???????????? 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).