0

I'm open to any dirty trick here, no matter how unclean or ill-advised they are.

My users query my server via Excel. I wish for the users to be able to trigger an agent job from Excel. They only have the 'SELECT' and 'View Definitions' permission. To the best of my awareness, this means that I must devise some method by which my users can trigger an agent job via querying a view. How can this be done?

J. Mini
  • 1,868
  • 1
  • 9
  • 38
  • No, it cannot be. – Thom A Apr 13 '22 at 14:47
  • Looks like impossible task – Sergey Apr 13 '22 at 14:48
  • 1
    You are aware that it's possible to assign *individual* permissions as well as database/schema wide ones, aren't you. Give the users a *narrowly tailored* permission to do something else, such as an insert to one table specifically built for this functionality. (And if the argument is that they cannot be granted any further permissions, then surely this idea breaks the *spirit* of that prohibition if not the letter) – Damien_The_Unbeliever Apr 13 '22 at 14:49
  • Beyond the scope of the technology. Even if it was possible, the permissions needed alone would not be acceptable. – tonyd Apr 13 '22 at 14:49
  • You wanted ugly.... Have something external monitoring a trace that looks for SELECT's from your users, then when it finds one it would then fire off the job. – squillman Apr 13 '22 at 14:50
  • 1
    The `LOGIN` must have a mapped `USER` in the `msdb` database, and that user must have the relevant roles in the database; this might be `SQLAgentOperatorRole` or it might be `SQLAgentUserRole` (depending on who owns the job). – Thom A Apr 13 '22 at 14:51
  • Otherwise, you could have a signed procedure that they use in a user database that uses a certificate with the needed credentials in `msdb`. – Thom A Apr 13 '22 at 14:52
  • You can use an Excel Macro: see https://www.datanumen.com/blogs/call-sql-server-stored-procedure-excel-vba/ –  Apr 13 '22 at 15:10
  • I concur with @Damien_The_Unbeliever : create a procedure that fires the job, give permission to users to `EXECUTE` that procedure. Anything else will be ugly in the extreme – Charlieface Apr 13 '22 at 15:44
  • @Damien_The_Unbeliever Is there any clean way to do this? I can't think of a nice way to give my users permission to run `sp_start_job` without giving them the terrifying permission of allowing them to run any job on the server. – J. Mini Apr 15 '22 at 11:46
  • @Charlieface Are you sure that actually works? I tried it, but the user needed permissions on `sp_start_job`. – J. Mini Apr 15 '22 at 11:47
  • @J.Mini - impersonate a proper execution account inside the stored procedure: https://learn.microsoft.com/en-us/sql/t-sql/statements/execute-as-transact-sql?view=sql-server-ver15 – Alex_404 Apr 20 '22 at 13:00
  • Perhaps create a stored procedure (give execute permissions to your users). In the stored procedure add an item to a database service broker queue. Create a separate job which reads the queue and when an item arrives in the queue execute your step (or call another job), then wait on the queue again. – Steve Ford Apr 21 '22 at 22:01

1 Answers1

2

You could set up an SQL job that monitors (runs every X minutes?) query history: How to see query history in SQL Server Management Studio and as soon as it finds the query on the view - it writes down the execution timestamp and triggers the job you want to run. Next run it looks for the same query but triggered after the saved timestamp.

This would be more "safe" if you have a proper monitoring tool already running on the server.

But you should really consider a better solution for this.

EDIT: Based on your comments - create a stored procedure that triggers the job. Grant your users execution rights on the stored procedure. Inside the procedure use context switching to be able to run the job without giving the users rights to run any job - just switch to a service account: https://learn.microsoft.com/en-us/sql/t-sql/statements/execute-as-transact-sql?view=sql-server-ver15

Alex_404
  • 399
  • 2
  • 12
  • Is it possible the OP could use eXtended Events to watch for the specific select command, write that to a table. The table has a trigger which could execute the job? – RobLW Apr 19 '22 at 05:50
  • I'm aware of `EXECUTE AS` and other impersonations, but I'm under the impersonations that `sp_start_job` in particular is very picky about these things and tends to ignore tricks like `EXECUTE AS OWNER`. Am I wrong? – J. Mini Apr 20 '22 at 20:17
  • @J.Mini - it should work. In the worst case you can get a "not trusted impersonator" error but then you should just solve that, as I remember without googling by setting your database as "trusted database" so that msdb accepts impersonation. Edit: also, try not only "AS OWNER" but as another account as well. – Alex_404 Apr 26 '22 at 14:01