There are some days like that, I feel a bit nostalgic : I’m not anymore a full-time DBA since two years.
Today, what made me think of that distant time was a simple question: how to allow a user to execute all stored procedures in a specific database? The answer is simple : create a new role and assign it to the database user. The beauty is that it’s even easier to do than to say!
USE myDatabase;
GO
-- create a db role to execute stored procedures
CREATE ROLE db_spexecute;
-- grant execute to role
GRANT EXECUTE TO db_spexecute;
-- assign role to user
EXEC sp_addrolemember N'db_spexecute', N'myUser';
GO