Setting up a role with execute permissions using SSMS

I have always created roles with the execute permissions only through SQL scripts and the reason for this is that I could never figure out how to do it via Sql Management Studio. Well today I figured it out and so here are the here are the steps for setting up a role with execute permissions using Sql Server Management Studio (SSMS).

Why? If you want to control what permissions your users have to the database and want to control it through roles. I used it for defining a separate role that would be used if users needed to have execute permissions on all the stored procs in the database (instead of – per stored proc).

1. Right click on the roles node under security for your database and select Add New Database Role.
2. Setup the General properties page
   
3. On the securables page, select Search and select all objects of type and click OK
   
4. In the next window, select databases and click OK
   
5. Back on the securables page, select the database row that just got added and in the Permissions for {DatabaseName}, select grant for the Execute permission.
   

When you click ok, your role will be created and the steps will be similar to the following script.

USE [databaseName]
GO
CREATE ROLE [db_execute_role] AUTHORIZATION [dbo]
GO
use [databaseName]
GO
GRANT EXECUTE TO [db_execute_role]
GO

Note: this may not be the most secure for your purposes, but the steps can easily be modified to reduce the level at which the execute permissions are provided. (these steps define it at the database level).