存储架构

How do I let someone execute a SQL Agent job they don’t own?

微信扫一扫,分享到朋友圈

How do I let someone execute a SQL Agent job they don’t own?
0

In my last post I discussed the fact that SQL Ids can, in fact, be useful on an instance that is set to Windows Authentication only.
Today I’m going to use that same technique to give someone permission to run a SQL Server Agent job without granting them excessive permissions.

Quick background. There are only two ways that someone can have permission to execute a SQL Agent job. You must either own the job, or be a member of the role SQLAgentOperatorRole
(found in msdb
). Unfortunately SQLAgentOperatorRole
grants permissions to run any
job (among other things).

However, by creating an id with the permissions to run any job and then creating a stored procedure that uses impersonation to use that id to run the job we can work around this.

USE msdb;
GO
-- Create a user to run the job.
CREATE USER CanRunAnyJob WITHOUT LOGIN;
-- Add user CanRunAnyJob to the role SQLAgentOperatorRole
ALTER ROLE SQLAgentOperatorRole ADD MEMBER CanRunAnyJob;
GO
-- Create a stored procedure that executes the job
CREATE PROCEDURE sp_RunThisJob
WITH EXECUTE AS 'CanRunAnyJob'
AS
EXEC sp_start_job 'ThisJob';
GO

Now, in order to test we have two options. Either test using a windows/AD id by running SSMS as an alternate ID
or create another SQL Id and use impersonation again. I’m using the SQL Id here but I did test this both ways and it worked fine.

-- Create a SQL Id with no permissions other than to run 
-- the stored procedure.
USE msdb;
GO
CREATE USER CanRunAJob WITHOUT LOGIN;
GO
GRANT EXECUTE ON sp_RunThisJob TO CanRunAJob;
GO
-- Impersonate the user and run the job.
EXECUTE AS USER = 'CanRunAJob';
GO
EXECUTE sp_RunThisJob;
GO
REVERT;

A few things of note here.

  • The job I used was a very simple job. It is owned by sa
    with a single step that ran a single T-SQL statement:

    SELECT name FROM sys.databases;

    I believe this will work regardless of the owner of the job or the tasks the job is running, however, if you run into problems remember thatproxies will allow you to specify exactly who is running each step of a job.

  • The SQL Ids I created didn’t have an associated server principal (login). This means that there is no password, no instance level CONNECT SQL
    permission, etc. Basically, regardless of if the instance is in Mixed mode
    or Windows Authenticated
    mode you can’t use these ids to connect to the instance. I did this deliberately to limit any security risks.
  • Everything I did here was in msdb
    . This is because the permissions needed were all there. When dealing with this in a real environment your CanRunAnyJob
    user will have to be created in msdb
    so that it can be added to the SQLAgentOperatorRole
    role. That means the stored procedure also has to be created in msdb
    . And finally, anyone that is going to have access to run that stored procedure will need a user within msdb
    . I don’t see this as a major risk since the only
    permissions they will be granted is EXECUTE
    on that stored procedure.
阅读原文...

微信扫一扫,分享到朋友圈

How do I let someone execute a SQL Agent job they don’t own?
0
SQL Studies

《Apex 英雄》移植 NS?总监:目前无法做任何承诺

上一篇

IE 的安全性漏洞让骇客得以窃取档案

下一篇

评论已经被关闭。

插入图片

热门分类

往期推荐

How do I let someone execute a SQL Agent job they don’t own?

长按储存图像,分享给朋友