Should you store your SQL Stored Procedures in Source Control?

Yes. All code should be stored in source control.

Simply put, code is code and mistakes happen. It's nice to be able to go back and see what changed over time and be able to go back to those changes.

We have to add it manually to a source control system, but you can create addons for the Sql Server the Management System. I haven't ever created one to automatically add it to source control, but I suppose you could. Also, all the code is stored in sql tables, so you could in theory create a process or something to go through the table(s) and retrieve all the code and commit it automatically.

Update: I would always write extra code to check and see if the code exists and if it doesn't create a filler procedure and then the actual script do and alter procedure.

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[SomeStoredProcedure]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)EXEC sp_executesql N'CREATE PROCEDURE [dbo].[SomeStoredProcedure] ASSELECT ''SPROC Template'''GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO ALTER PROCEDURE SomeStoredProcedure

Doing a drop and recreate will remove all the user permissions you have setup for it.

ABSOLUTELY POSITIVELY WITHOUT QUESTION NO EXCEPTIONS IN ALL PERPETUITY THROUGHOUT THE UNIVERSE YES!

Get your database under version control. Check the series of posts by Scott Allen.

When it comes to version control, the database is often a second or even third-class citizen. From what I've seen, teams that would never think of writing code without version control in a million years-- and rightly so-- can somehow be completely oblivious to the need for version control around the critical databases their applications rely on. I don't know how you can call yourself a software engineer and maintain a straight face when your database isn't under exactly the same rigorous level of source control as the rest of your code. Don't let this happen to you. Get your database under version control.