Création de procédures stockées dans SQL Server

Je travaille sur une assez grande application dans laquelle un schéma de database (tables, procédures stockées, etc.) est créé en exécutant un script SQL. Le script est appelé à partir d'une procédure stockée via xp_cmdshell comme ceci.

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

Cette procédure stockée est exécutée une fois lors de l'initialisation du programme et à nouveau par SQL Server Agent pendant un travail planifié.

Pour des raisons de security, nous devons désactiver xp_cmdshell. Ma première inclination était d'insert simplement toutes les commands de création de schéma dans la procédure stockée existante afin qu'elle puisse être exécutée au besoin. Toutefois, cette approche ne fonctionnera pas car SQL Server ne prend pas en charge la création de procédures stockées à partir d'autres procédures stockées.

Une alternative pourrait être d'appeler le script de création de schéma à partir d'une application externe plutôt que d'un script SQL, mais cela nécessite une réécriture assez importante, que nous essayons d'éviter si possible.

Y a-t-il un moyen d'accomplir cela avec un minimum de changements?

Ma première inclination était d'insert simplement toutes les commands de création de schéma dans la procédure stockée existante afin qu'elle puisse être exécutée au besoin. Toutefois, cette approche ne fonctionnera pas car SQL Server ne prend pas en charge la création de procédures stockées à partir d'autres procédures stockées.

Si vous avez vraiment besoin d'encapsuler la création de procédures stockées dans une autre procédure de stockage, vous pouvez l'inclure dans un SQL dynamic:

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

Bien sûr, vous devrez échapper à chaque ' SQL dynamic interne.


Une autre façon consiste à exeute osql directement à partir du travail SQL Server Agent:

entrez la description de l'image ici

Vous devez vérifier si le count de l'Agent SQL Server dispose de privilèges suffisants ou créer un proxy.

MODIFIER:

Si vous ne pouvez pas utiliser l'Agent SQL Server, utilisez le Planificateur de tâches Windows pour exécuter osql:

  • Démarrer le planificateur de tâches

  • Planifier une tâche

Nous sums dans la même situation que vous, par laquelle nous voulons désactiver xp_cmdshell , que nous avons utilisé pour un certain nombre de choses sur nos servers de production.

Ce que nous avons fait était de créer des methods SQLCLR pour les opérations que xp_cmdshell effectuées dans notre environnement. Bien sûr, l'assemblage devait être créé comme UNSAFE , mais les methods ne pouvaient être appelées que pour les opérations individuelles, et les methods avaient beaucoup de code de validation, de sorte que nous n'avons rien fait de "stupide".