There are different ways to grant user permission to alter, view or execute stored procedures.
The easiest approach I found was to create a "Database Role".
Name anything you like. I liked "db_executor" role.
Now give this role permissions and then add users to this role.
So..
/* Create a new role for stored procedure permissions */
CrEATE ROLE db_executor
/* Grant stored procedure Alter/View/Execute rights to the role */
GRANT ALTER, VIEW DEFINITION, EXECUTE TO [db_executor]
/* Add a user to the db_executor role */
EXEC sp_addrolemember 'db_executor', [domain\userid] -- No single quotes around user.
The easiest approach I found was to create a "Database Role".
Name anything you like. I liked "db_executor" role.
Now give this role permissions and then add users to this role.
So..
/* Create a new role for stored procedure permissions */
CrEATE ROLE db_executor
/* Grant stored procedure Alter/View/Execute rights to the role */
GRANT ALTER, VIEW DEFINITION, EXECUTE TO [db_executor]
/* Add a user to the db_executor role */
EXEC sp_addrolemember 'db_executor', [domain\userid] -- No single quotes around user.
