SQL Server '05: Adding New Users and setting permissions

[aconybeare]

Hi,

I have a database that has been using a user who has admin priveleges, now that the app. is moving into production I want to create a new user with cut down permissions, does anyone have any ideas on the best way of creating this user so that they can execute stored procedures etc.

Do I need to go through and execute the following for each sp? (+/-350 yikes)

 
GRANT EXECUTE ON [proc_name] TO [user]

Additionally how do I manage this going forward? Do I have to add the above after I create or drop and recreate my procedures?

USE myDB
Go
If Exists (SELECT * FROM sysobjects WHERE ID =
   OBJECT_ID('up_procname') AND sysstat & 0xf = 4)
   DROP PROCEDURE up_procname
Go
CREATE PROCEDURE up_procname(
   @Jnl_Title varchar(250) OUTPUT,
   @Jnl_Code varchar(10),
   @Status varchar(20) = 'A'
)
As
   SET NOCOUNT ON 
   SET @Jnl_Title=
   	(
   		SELECT j.Jnl_Title
		FROM Jnl_Config j
		WHERE j.Jnl_Code=@Jnl_Code
	)

   Return @@Error
   SET NOCOUNT OFF
GO
GRANT EXECUTE ON up_procname TO [myuser] 
GO

Any guidance would be appreciated

Cheers Al