I have a couple of stored procedures that will be used only for maintenance tasks (facilitating truncation of tables, rebuilding indexes etc). In my mind the natural location for these would be the master DB (or any other central DB) so that other DBs would be able share this functionality.

This would also clarify the intent of these procedure and excludes these from DACPAC deployments. However, in Azure SQL cross-database queries are not allowed so I do not find any natural place to put these procedures inside.

Where would you put these kinds of stored procedures that are outside the actual business needs?

CodePudding user response：

You can always create a schema, e.g. called something like admin and put them in there.

See Create a Database Schema on Microsoft docs for more details.

Then you can execute them from within the database itself, and a schema is also a great mechanism to regulate permissions - giving execute permissions on these stored procedures only to your DBA staff and now users of the regular database.