Creation of stored procedures in SQL Server

Question

I'm working on a fairly large application in which a database schema (tables, stored procedures, etc) is created by running a SQL script. The script is invoked from a stored procedure via xp_cmdshell like this.

CREATE PROC CreateNewScheme AS
BEGIN
    ...
    EXEC xp_cmdshell 'osql createschema.sql -U username ...'
    ...
END

This stored procedure is run once during program initialization and again by SQL Server Agent during a scheduled job.

For security purposes we are required to disable xp_cmdshell. My first inclination was to just insert all of the schema creation commands into the existing stored procedure so it can be that can be executed as needed. However, this approach will not work because SQL Server does not support creation of stored procedures from other stored procedures.

One alternative might be to call the schema creation script from an external application rather than from a SQL script, but that's requires a fairly large rewrite, which we're trying to avoid if possible.

Is there a way to accomplish this with minimal changes?

Lukasz Szozda · Answer 1 · 2017-08-09T16:02:08.133

2

My first inclination was to just insert all of the schema creation commands into the existing stored procedure so it can be that can be executed as needed. However, this approach will not work because SQL Server does not support creation of stored procedures from other stored procedures.

If you really need to wrap creation of stored procedures inside another store procedure you could wrap it with dynamic SQL:

CREATE PROC CreateNewScheme AS
BEGIN
    EXEC('CREATE PROCEDURE myfirstproc AS ...');

    EXEC('CREATE PROCEDURE mysecondproc AS ...');
END

Of course you will have to escape every ' inside dynamic SQL.

Another way is to exeute osql directly from SQL Server Agent job:

You should check if SQL Server Agent Account have sufficient privileges or create proxy.

EDIT:

If you cannot use SQL Server Agent, use Windows Task Scheduler to run osql:

edited Aug 09 '17 at 16:02

answered Aug 08 '17 at 19:40

Lukasz Szozda

162,964
23
234
275

The dynamic SQL approach seems a bit messy since I would have to wrap over 100 stored procedures, some of them fairly complex. This would be time consuming and error prone. I like the idea of using SQL Server Agent though. – Mike Collins Aug 08 '17 at 20:01
According to Microsoft, there is a way to execute SQL Server Agent jobs directly from q SQL script. This might be a good solution is there's a way to wait for the job to complete before continuing. – Mike Collins Aug 08 '17 at 20:03
It turns out that using SQL Server Agent was not a viable option either as it provides access to the command line. Because of that it's considered a security vulnerability and will be turned off. – Mike Collins Aug 09 '17 at 14:37
@MikeCollins How about Windows Task Scheduler? It is independent from SQL Server and is part of OS. – Lukasz Szozda Aug 09 '17 at 16:02

score 1 · Answer 2 · answered Aug 09 '17 at 06:04

We are in the same situation like you, whereby we want to disable xp_cmdshell, which we used for a couple of things on our production servers.

What we did was to create SQLCLR methods for the operations that xp_cmdshell performed in our environment. Sure, the assembly had to be created as UNSAFE, but the methods could only be called for the individual operations, and the methods had a lot of validation code, so that we didn't do anything "stupid".

Creation of stored procedures in SQL Server

2 Answers2