The EXECUTE permission was denied on the object 'xxxxxxx', database 'zzzzzzz', schema 'dbo'

Question

Sounds like you need to grant the execute permission to the user (or a group that they a part of) for the stored procedure in question.

For example, you could grant access thus:

USE zzzzzzz;
GRANT EXEC ON dbo.xxxxxxx TO PUBLIC

Best solution that i found is create a new database role i.e.

CREATE ROLE db_executor;

and then grant that role exec permission.

GRANT EXECUTE TO db_executor;

Now when you go to the properties of the user and go to User Mapping and select the database where you have added new role,now new role will be visible in the Database role membership for: section

For more detail read full article

In SQL Server Management Studio, go to security->schema->dbo:

Double-click dbo, select the Permissions page, then click the "View database permissions" link in blue:

Select the user for whom you want to change permissions, and look for the "Execute" permission under the "explicit" tab:

Choose the appropriate permission by checking the appropriate box.

you need to run something like this

GRANT Execute ON [dbo].fnc_whatEver TO [domain\user]

This will work if you are trying to Grant permission to Users or roles.

Using Microsoft SQL Server Management Studio:

1. Go to: Databases
2. Right click on dbo.my_database
3. Choose: Properties
4. On the left side panel, click on: Permissions
5. Select the User or Role and in the Name Panel
6. Find Execute in in permissions and checkmark: Grant,With Grant, or Deny

Giving such permission can be dangerous, especially if your web application uses that same username.

Now the web user (and the whole world wide web) also has the permission to create and drop objects within your database. Think SQL Injection!

I recommend granting Execute privileges only to the specific user on the given object as follows:

grant execute on storedProcedureNameNoquotes to myusernameNoquotes

Now the user myusernameNoquotes can execute procedure storedProcedureNameNoquotes without other unnecessary permissions to your valuable data.