Running SQL Server Agent jobs on demand by unauthorized users
来源:互联网 发布:php棋牌游戏开发构架 编辑:程序博客网 时间:2024/06/04 22:47
http://giladka8.blogspot.sg/2012/05/running-sql-server-agent-jobs-on-demand.html
PROBLEM
You configure some SQL Server Agent jobs to run schedule maintenance tasks, but there is sometimes the need for these tasks to be executed on demand.You want other users just to run the job, without modifying it.
For those users SQL Server Agent node in Object Explorer is not visible because they have minimum permissions (no SQLAgentOperatorRole)
SOLUTION
Use database impersonation by using EXECUTE AS
USE [master]
GO
CREATE LOGIN [runSqlAgentJobsLogin] WITH PASSWORD=N'123%123',DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
USE [msdb]
GO
CREATE USER [runSqlAgentJobsLogin] FOR LOGIN [runSqlAgentJobsLogin]
GO
EXEC sp_addrolemember N'SQLAgentOperatorRole', N'runSqlAgentJobsLogin'
GO
CREATE PROCEDURE [RUN_BACKUP_SP] WITH EXECUTE AS 'runSqlAgentJobsLogin'
AS
EXEC sp_start_job @JOB_NAME = 'My_Backup'
GO
GRANT EXECUTE ON [MSDB].[DBO].[RUN_BACKUP_SP] TO [DOMAIN\User]
GO
Now, connect as an unauthorized user to one of the databases and run the following. The user can only run the procedure that runs the job, he cannot see the job or modify it
EXEC [MSDB].[DBO].[RUN_BACKUP_SP]
- Running SQL Server Agent jobs on demand by unauthorized users
- Are SQL server Jobs Currently Running?
- SQL server, get all running sessions on the SQL server
- sql server代理(Jobs)
- SQL SERVER – Get Last Running Query Based on SPID
- SQL Server Logins and Users - Orphaned Users
- SQL SERVER AGENT SECURITY
- Running Background Jobs in Ruby on Rails Revisited
- SQL Server数据库里的Management里的SQL Server Agent里的Jobs是个定时运行功能(死亡;历险)
- Server already running on localhost
- SQL Server Express 没有 SQL Server Agent
- statement by the president on thepassing of steve jobs
- Windows Vista On Demand
- game on demand
- 视频点播 video on demand
- Loading Views On Demand
- On-Demand Resources Essentials
- initialization on demand holder
- dojo dnd
- 简单工厂与策略模式的结合
- Errore HTTP 404.2 - Not Found" IIS 7.5 请求的内容似乎是脚本,因而将无法由静态文件处理程序来处理
- ASP.NET Web.config配置文件的基本使用方法
- 几种常用数据访问的关系
- Running SQL Server Agent jobs on demand by unauthorized users
- 哈希函数
- blockUI插件
- 自己用的工具类(一)
- Red hat vi/vim常用的命令
- 控制文件的恢复(二)
- 晶晶实验七_之事务表篇
- 自动化测试基础(精华版)
- 晶晶实验八_DML语句时回滚和重作的工作流程