Recently someone was asking how they could execute some ALTER DATABASE commands without requiring sysadmin permissions. This person's company didn't allow sysadmin to be granted to non-DBAs, and they had to recover a series of applications and wanted to do that with some type of application setup instead of scripting for the DBAs.

CAUTION: Before you use this technique, really investigate whether or not you need to actually do this or if there is another way. This can be a big security hole.

My suggestion is that you place the code in a table, or script a series of inserts into a table. This can be a simple table with an ID, a description, and the code. I'd use something like this:

create table ExecutableCode

( codeid int identity(1,1)
  , codedescription varchar(1000)
  , codeitself varchar(max)

)

I use an ID just for reference, and then I'd include a description, such as

insert ExecutableCode select 'This code creates a database', 'create database Mydb'

That creates a single line of code that will use the defaults for building a database. Obviously you can write more complicated code if need be.

The next step is to create an execution table

create table ExecuteLog

( Logid int identity(1,1)
  , codeit int
  , executestart datetime
  , executeend datetime
  , executionneeded tinyint

)

The purpose of this table is to both log the execution and flag it. If I wanted to execute the inserted code, I'd need the ID, which I'll assume is 1. In that case I'd insert

insert ExecuteLog select 1, null, null, 1

I use Nulls for the dates since I'm not actually executing the job. I'm telling someone else to execute it. That someone else is SQL Agent. Now in order for this to work, the Agent that is executing the job, or a proxy, needs to have the sysadmin permissions (or whatever elevated permissions are needed). What I do is create a proc that looks for a job to execute and executes it. In that job I want to flag the start and end of the job for logging, so the psuedocode is:

-- Get codeID to execute from ExecuteLog

-- update ExecuteLog for that line, update start time

-- execute code from ExecutableCode using the codeId to retrieve the statement

-- update ExecuteLog for that line, update end time, mark execution needed as 0

A few caveats here. First, be sure that you don't allow anyone to just put code in the ExecutableCode table. That could be a big security risk. Second, I'm not sure this makes sense for one time events, like DR. To me the DBA can just run this since they'll need to script the creation in any case. Third, you may still be limited, depending on how your SQL is structured. The EXEC() command has limited permissions, and while you can use XP_CMDSHELL or SQLCMD in Agent, that could be more of a security hole if you don't set it up properly

That's it. It's essentially what the Windows scheduler does, and in this case it allows you to work with SQL code.