Sql Server 2008 R2 User Cant Use System Procedure

I have restored a 2005 Database to a new 2008 R2 DB Server. I have created a new user called 'gatekeeper' However this user does not have access to the list of stored procedure in

Solution 1:

I am honestly a bit confused by your description, so please let me know if I am off base here. You can try this below. It will grant execute access to all stored procedures. If you only need gatekeeper to have access to the one stored procedure, then just grant execute on that stored procedure. If the stored procedure is accessing data from other databases, you may need to grant permissions on the tables and such that it is accessing outside of its database.

CREATE ROLE db_executor
GRANT EXECUTE TO db_executor
EXEC sp_addrolemember 'db_executor', 'gatekeeper'