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]
If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!
