Mikael Wedham

Why?

Sometimes you have tasks that need high permissions on your SQL Server. Naturally you can run them yourself (since you apparently is sysadmin…) and you could also schedule the scripts. But what if scheduling isn’t good enough. The database isn’t updated for days (script runs every 5 minutes, killing performance) and then the user doen’t have the patience to wait for 5 minutes, until the next “load” schedule is due. It’s a loose-loose situation.
A common solution is to give the user sysadmin rights, but it is more often than not – A BAD IDEA!

Suggested solution:

Categories

I suggest you add a category for the jobs you want to handle like this. I also show how to create a category for the Alert, but if you don’t want to do it there is an alert category named [Uncategorized] that you can use. (The default category for jobs are [Uncategorized (Local)])

IF NOT EXISTS (SELECT name FROM dbo.syscategories WHERE name=N'Jobs executed by alerts' AND category_class=1)
BEGIN
EXEC dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Jobs executed by alerts'
END
IF NOT EXISTS (SELECT name FROM dbo.syscategories WHERE name=N'Admin Alerts' AND category_class=2)
BEGIN
EXEC dbo.sp_add_category @class=N'ALERT', @type=N'NONE', @name=N'Admin Alerts'
END

The job

You can create a job in the SQL Server Agent, owned by [sa] just as you would do when you want to schedule the script normally. Add steps as needed and test the job to make sure it works as expected.

DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'Job That Requires Sysadmin', 
		@description=N'This job has a task that must be started by a user who isn''t sysadmin.', 
		@category_name=N'Jobs executed by alerts', 
		@owner_login_name=N'sa', 
		@job_id = @jobId OUTPUT

EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, 
        @step_name=N'DBCC CHECKDB', 
		@step_id=1, 
		@subsystem=N'TSQL', 
		@command=N'DBCC CHECKDB (''master'')', 
		@database_name=N'master'

EXEC msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

Sample result

The Alert

To be able to trigger the job, you will need an Alert. The alerts can react on different events in the SQL Server, however in this article I will focus on Error Numbers. Alerts only respond to error numbers that are written to the Windows Event log, so we need to begin with adding an error message that writes to the Event log by default. That is accomplished with the @with_log = ‘TRUE’. The @msgnum (the Error number) must be an integer ranging from 50001 to the upper [int]-datatype limit.

EXEC sp_addmessage @msgnum = 1337000, @severity = 10,   
   @msgtext = N'The Login named [%s] required Job [Job That Requires Sysadmin] to be run.',   
   @with_log = 'TRUE', @replace = 'REPLACE'

And now there is only the alert creation left. First you would need the Job Id of the job:

SELECT job_id FROM sysjobs WHERE name = 'Job That Requires Sysadmin'

With this information, you just create the alert, and connect the Alert to the Job with the @job_id:

EXEC msdb.dbo.sp_add_alert @name=N'Alert that listens to 1337000', 
		@message_id=1337000, 
		@delay_between_responses=30, 
		@category_name=N'Admin Alerts',  
		@include_event_description_in=0, 
		@job_id='3DF78369-EF6B-4212-871E-2B4DF8AB5E4E'
	GO

Sample result:

The @delay_between_responses parameter makes the Alert wait before it triggers the job again. This is a very good idea when the Job takes a while to run. Make sure you time the job execution (from when you tested that it worked) and set the delay time accordingly.

Running the job!

Connected to the SQL Server as pretty much any user. Run the following command:

RAISERROR(1337000, 10, 1, 'Wedham_Demo_User');

Naturally, you can send any string as the last parameter. The string is inserted into the error message in the position of %s. This message will show up in the Windows Event log.
Feel free to run this from a Command prompt using sqlcmd, or from a custom made C# application, anything that lets a user connect to SQL Server will do!

sqlcmd /S. /Usql /Psql /Q"RAISERROR( 1337000, 10, 1, 'Wedham_Demo_User');"
Yes, I know. Seriously bad password...