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

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

TechEd Europe 2014

Leave a comment

I have been selected to staff at TechEd in Barcelona. So if you are there, go to the Data Platform Booth. That’s where I’ll be standing answering questions all day long. I’m also representing SQL Server at the “Ask the experts” session once again. That’ll be really exciting! Before that I hope to make it in time for the SQL Server Saturday that also is in Barcelona.

Did you attend any Microsoft class and need the lab files?

Leave a comment

Well, they are a bit difficult to get from the virtual machines, and almost impossible if you have been using LabsOnline.

Microsoft has compiled a page with a lot of lab files and setup scripts here:

I guess they are pretty useless without the lab instructions, but feel free to download them 🙂

And speaking of download… The SQL Server sample database location is still codeplex…

Happy sampling

Restore Master

1 Comment

You’ve just rebuilt your master database. You discover that your other databases are gone, all logins are missing, pulse is rising…

If you take regular backups of your system databases (master and msdb primarily) you have nothing to worry about. If your database files are on undamaged drives you are even better up.

To get all the logins back and all of your user databases “re-attached” you only need to restore your master database. And restoring the msdb database gets you the jobs, schedules and alerts back as well.

To tell the truth: This is not something you do every day, it may even be a bit scary. So here is a simple way to restore your master database, that works in SQL Server 2008, 2008 R2, 2012 and in 2014.

NOTE: commands are written against the default instance (named MSSQLSERVER). If you have a named instance, you’ll have to find the name of it first…

1. Start an elevated command prompt.

2. Run : net stop mssqlserver

3. Navigate to your instance’s “binn” folder (i.e. “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn”)

4. Run : sqlservr.exe -m -c -s MSSQLSERVER
This starts sqlservr (the server executable) in single user mode (-m) as a console/not service application (-c). And the instance name is MSSQLSERVER (the default instance)

!I got a warning from Windows built in firewall at this step. It’s perfectly OK to ignore, and NOT let the application communicate on networks – we’re local so the restore will work anyway.

5. Start ANOTHER Command prompt – DO NOT close the other one.

6. Run : sqlcmd -s. –E
SQL Server command tool logs in to the default instance, locally (-s.) with Windows Authentication (-E)

7. 1>restore database master from disk = ‘full path to backup file ‘

Your other window should now be back at the command prompt. Database restored.

Since the method uses only console tools, it works fine on servers with sparse UI possibilities Ler

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.

Prepairing for SQL Saturday

Leave a comment

Copenhagen is the location for this free event about SQL Server. My session this time is about LSN and the different backup combinations. How you can mess things up by yourself and how to avoid it…