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

I'm having problems executing a function.

Here's what I did:

1. Create a function using SQL Server Management Studio. It was successfully created.
2. I then tried executing the newly created function and here's what I get:

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

Solution 1:

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

Solution 2:

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

Solution 3:

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.