. . .

The use of a database is always a good idea and not giving users any direct access to the tables is an even better idea but wrapping the tables with stored procedures that you can secure up by group membership is a little bit of a pain, this script will enable you to create all of the CRUD stored procedures, you can control how the script runs via the options.

You can set all of the following:

set @Author = SUSER_NAME()
set @SPPrefix = ‘usp_’
set @Schema = ‘dbo’

set @createInsert = 1
set @createUpdate = 1
set @createDelete = 1
set @createSelect = 1
set @createSelectByPrimaryKey = 1

set @DisplaySQL = 0
set @ExecuteSQL = 1

Use the @ExecureSQL & @DisplaySQL options to generate a script that you can look at and run later.

Download file : CRUD.sql as a text file