Thanks a lot Grant. That was really helpful. A followup question to this is
Once the developers create content on the dev server(functions/ stored procedures)which is currently integerated with the website running on the same machine under admin privileges, I need to migrate this to a production machine where the website will not be running under admin privileges.
What credentials should I provide for such a website such that it is able to execute the stored procedures/view tables etc in the database.
Short answer: It depends.
Longer answer: You need to give it access to the server. Then you need to connect that login to the server with a user in a database. I personally have the users belong to a role and then I grant permissions to execute procedures or read or write data to the role, never to any users. Other people do it different ways.
What is the best strategy to move newly created content over. By content I mean mostly tables, stored procedures, functions and data stored in the tables.
Since we will be updating the database monthly(the users will be entering data as well) I am not sure if we can overwrite the whole database from dev machine to production machine.
Again, it depends. The approach I've been using for years & years is to develop a migration script (or scripts), test it on a copy of production until I'm sure it moves everything I need it to move and doesn't blow up the production data and then I run the script against production. This means no ad hoc scripting on production, which is strictly a check against me, because, given the chance, I'll screw up somehow.
Generating this script is another pain point. You can do it manually. You can try doing it using Management Studio tools, or, I'd suggest getting a third party tool. Apex has a comparison utility that's supposed to be pretty good. There's another one called DBGhost that I've tested. It worked well and more importantly, worked out of source control. One of my favorites is still Red Gates SQL Compare. It works out of source control as well as database to database. If you really want to get into it though, you pick up a copy of Microsoft's Visual Studio Team System Database Edition (free if you already have a copy of Team System Developer Edition) and then build and deploy your database exclusively from source control, just like the developers do.
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore RooseveltThe Scary DBA
Author of: SQL Server Query Performance Tuning
and SQL Server Execution Plans
Product Evangelist for Red Gate Software