How to execute server-level commands without Server admin rights

Question

How to execute server-level commands without Server admin rights

martin servercentral ares

SSC Rookie

Points: 32
More actions
August 27, 2009 at 2:41 pm

#225542

Hi,
I am actually building an engine to script the recovery of all of our applications given a disaster recovery scenario. We have DBs in mirroring, others in Failover mode. Two stored procs were built to execute required steps to recover from the "alternate" server.
The engine, built in c#, needs to connect to the DB and run those scripts. Inside these scripts, there are commands like "Alter Database", Update... Unfortunately, we need SQL server level rights to be able to switch the DBs. In our corporate environment, this kind of access is specifically restricted to DBA's.
How could I execute those SPs other than by having a "Generic ID" with SQL Admin privilege? We are using SQL 2005. I thought about "Execute AS...", but found out that it can only apply to CRUD operations.
Help is needed..
MA

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734564 More actions · Answer 1

You need admin rights for some things. no way around it.

What you could do, however, is put the commands in a stored proc, or even a table somewhere. Allow your engine the rights to create a flag that says "run this" and then have an Agent job with elevated permissions come along and execute things.

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 2

Steve's presented the typical best solution from a technical side, so I'm going to ask the procedural question. Why not have this scripted and have a DBA execute in a disaster situation? That gives more intelligence to the recovery process, especially as requirements change with respect to what must be up and by what point.

K. Brian Kelley
@kbriankelley

Steve Jones - SSC Editor SSC Guru Points: 734564 More actions · Answer 3

I'd agree with Brian here that you ought to examine if you really need to do this.

If you go my route, I'd be sure that you don't allow the code in the table or proc to be changed by someone. Just allow them to set a flag in that (or preferably, another) table and execute based on that. Allowing them to change the command can be a SQL Injection risk.

martin servercentral ares SSC Rookie Points: 32 More actions · Answer 4

Thanks to both of you.

The reason why I'm trying to automate all of this is that we have approximately 75 .Net web applications that need to be recovered in a short timeframe. For each application, several steps are required; Switch main DB server, change DNS settings, modify web.config, start websites.... Coordinating all these steps would be time consuming, and on top of that, our limited number of DBA colleagues have other duties to perform in a crisis than to just restore (not so) critical applications.

With this trick, I can now automate the last piece of the puzzle, while keeping the corporate security team happy.

You just made my day with this scheduled agent, this is genious!

Thanks,

MA

Lowell SSC Guru Points: 323507 More actions · Answer 5

~~would something like this be a good idea to create a stored procedure with those commands, and have EXECUTE AS with sysadmin rights, then grant rights to the proc for the regular user?~~

-=edit=-

gawd i missed where you said execute as can only do CRUD operations...i did not know that.

what happens if someone were to run your suite of commands when they really were not supposed to?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Jones - SSC Editor SSC Guru Points: 734564 More actions · Answer 6

In that case, what I'd do is set up a script that

a) creates a table after recovery in some database, could be tempdb if needed

b) populates this with code, each row can be a command to execute

c) creates a job to execute the code in this table

d) runs the job.

If SQL Agent is recovered with sysadmin persmission, or a proxy recovered, you can then run this without more permissions.

martin servercentral ares SSC Rookie Points: 32 More actions · Answer 7

As I'm closely monitored by the security group, my proposal included that the AD account used to access the SQl server be disabled at all time except for a Disaster Recovery. If I am the only one being able to append in this table during a specific timeframe, beside admins, then I believe the risk is mitigated. Following your proposal, here is what I am thinking of:

1- have the table and scheduled (every xx minutes) agent (disabled) created right now.

2- ON a DR event, have the agent enabled; This agent will monitor the table.

3- My scripts to add specific parameters (DB name) in the table.

4- Agent to execute a SP using parameters in table.

With this, I can switch any database at any given time since the agent could run for the whole DR duration.

MA