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

here is how to give permission for one user not public,

Direct Query:

Use MyDatabase Grant execute on [dbo].[My-procedures-name] to [IIS APPPOOL\my-iis-pool] Go

The general answer is to grant execute permission as explained above. But that doesn't work if the schema owner of SP is different to underlying objects.

Check schema owners by:

select name, USER_NAME(s.principal_id) AS Schema_Owner from sys.schemas s

To change the owner of an schema you can:

ALTER AUTHORIZATION ON SCHEMA::YOUR_SCHEMA TO YOUR_USER;

Examples:

ALTER AUTHORIZATION ON SCHEMA::Claim TO dbo
ALTER AUTHORIZATION ON SCHEMA::datix TO user1;

Finally if within your SP you are truncating a table or changing structure you may want to add WITH EXECUTE AS OWNER in your SP:

ALTER procedure [myProcedure] 
WITH EXECUTE AS OWNER

as

truncate table etl.temp

If you only need to grant a single function then (only db admin can do it):

1. Open Management studio
2. Find function/procedure you want to grant in Object Eplorer (dbname-Programmability-[Functions/Stored Procedures]-...)
3. Right click on function or procedure name and open Properties
4. In Properties select Permissions, add user (or schema) you want and Grant him Execute permission.

I believe this is most secure way how to do it because you only grant to user execution of this function. Nothing else!

I think you have to select the object you want to grant access to, then right-click, and select properties. Select permission on the modal window that will be displayed then click on Search, on the newly revealed window, select browse, select the user you want to grant access and click on ok. it will display for you a list of permission and the grant status, and then you can choose to grant or deny

This shows that you don't have access to perform any action on the specified database table. To enable this, Go to Security -> Schema and check.

you'd better off modifying server roles, which was designed for security privileges. add sysadmin server role to your user. for better security you may have your custom server roles. but this approach will give you what you want for now.

1. Object Explorer -> Server -> Security -> Logins
2. Right click on your desired user
3. Go to Server Roles on left hand side
4. Make sure sysadmin is checked
5. Hit OK and restart your SQL server

Good luck