Grant execute stored proc permissions to a user

In this post I would like to focus on granting a user with execute stored proc permissions who already has read, write and view any definition permissions.

1) Open SSMS–> In the object explorer select the database in which the role has to be created.

2)Go to View –> Object Explorer Details –>select the database  in which the role has to be created–> security–>Roles–>Database Roles

3)Right click and select New Database Role

Enter the role name as shown below

Grant Exec Permissions to a user1

4)click on the square button next to owner text area and select [dbo] as the owner as shown below and click ok.

Grant Exec Permissions to a user2

5)Now in the Members of this role section add the user who should be the member of this role (in other words enter the user who should have the execute permissions) and click ok.

Grant Exec Permissions to a user3

6)Now go to securables tab and click on the search button and select the specific objects as shown below and click on ok.

Grant Exec Permissions to a user4

7)Click object type in select objects page and select schemas.

Grant Exec Permissions to a user5

8)Type dbo in the “Enter the object names to select” section as shown below and click on ok.

Grant Exec Permissions to a user6

9)Click on the securable and for the explicit permissions for dbo select execute and check in the Grant section as shown below and click on OK.

Grant Exec Permissions to a user7

10)The user should now be able to execute stored procs

Hope this helps

Advertisements
This entry was posted in SQL Server DBA Stuff and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s