Run administrative scripts on demand as a regular user

Leave a comment


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:


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)
EXEC dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Jobs executed by alerts'
IF NOT EXISTS (SELECT name FROM dbo.syscategories WHERE name=N'Admin Alerts' AND category_class=2)
EXEC dbo.sp_add_category @class=N'ALERT', @type=N'NONE', @name=N'Admin Alerts'

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.

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', 
		@job_id = @jobId OUTPUT

EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, 
        @step_name=N'DBCC CHECKDB', 
		@command=N'DBCC CHECKDB (''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', 
		@category_name=N'Admin Alerts',  

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...

Another proud moment!

Leave a comment

I am happy, proud to announce that I am speaking at the Sqlday conference in Poland.

The conference as a whole will be amazing with a lot of fantastic sessions and speakers – and me! I am very humbled to be selected to participate in this group of experts. If you have nothing to do between May 15 and 17, join me and a lot of others. It’ll be worth your time!

My session details can be found here.

Summer and SQL Saturday with precon

Leave a comment

Summer is approaching and with that the long awaited time to schedule all sessions for the SQL Saturday in Gothenburg. This year we will have even more intresting sessions than last year and also a precon. The precon tracks are already out and the schedule will be set at the end of this week. The earlybird discount is running out, so grab your tickets soon!

And most of all: have a wonderful summer and be prepaired in August. I know I will…

SQL Saturday #536 PRECON!

Leave a comment

The second SQL Saturday #536 in Sweden is taking place on August 27. The location is at the same place as before (Scandic Opalen), but we have some exciting news.

There will be 2 amazing pre-conference sessions this year. The precon info is located at and the session links will take you directly to EventBrite, where the tickets sale occurrs.

Uwe Ricken will talk about how to Analyze and tune your SQL Server like a PRO

Mark Broadbent will deliver a Real World SQL Server High Availability Masterclass

SQL Saturday information

Leave a comment

Many things has happened…

Apart from arranging the first SQL Saturday in Sweden that took place in Gothenburg on September 5., I am very proud to have been selected speaker at the first SQL Saturday in Serbia. Sql Saturday #475. If you happen to have a Saturday (November 28.) to spare, please join me and many others for a wonderful event. How can I say it’ll be a wonderful event? Well, it’s an event about SQL Server. Need I say more? 🙂

I’ve learned a lot from arranging an SQL Saturday, and I will take the lessons learned and use them next year. Yes, there will also be an SQL Saturday Gothenburg in 2016 as well!

SQL Server 2014 event!

Leave a comment

In about two weeks I’ll be one of the “Titans” on stage at Informators event Clash of the Titans. If you want to see us (me and Mattias) in a “what feature is most useful” battle with the new SQL Server 2014 as starting point.
The date is April 28. The information is here and you are invited!


SQL Rally Nordic 2013

Leave a comment

I was again among the lucky few who was selected as speaker at the Nordic Rally. The Rally takes place near Arlanda airfield, Stockholm the 5-6 November. My talk is about the transaction log. Please come and check it out… There are of course more than 20 other interesting subjects and speakers making this a worthwhile investment.

Is SQL Server 2000 gone for good?

Leave a comment

According to Microsoft, the extended support for SQL Server 2000 is completely gone as of April 9. 2013.

As you can see in the table (follow the link below) there is NO date after 2013-04-09. That means that the support is officially over. There will probably be no more updates, security fixes or patches anymore – even if you would need it!

But the most important piece of information, in my opinion, is that the general support ended 8. april 2008. That fact leads me to presume that most consultants does not have a real reason to preserve the SQL Server 2000 specific knowledge cached in their brains. There are actually not that many consultants who master the 2000 anymore… 

Change it. Now. To something closer to 2012.

Show the Microsoft Lifecycle search…

So it’s official

Leave a comment

I’ve been working for myself for more than eight years now. I like it. The fact that I’ m my own boss. I decide what to do and what to buy 😊. However sometimes you need co-workers. You know, the persons you can talk to and ask stuff without the risk that they will think you are strange and will never hire you again…

I got the opportunity to work for SolidQ. A really “front line” kind of company. I will still be in business as usual, but I can also be reached at my new company. I havre already found this arangement useful and fun, and  and can’t wait to contribute to the SolidQ community.