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

Question

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'.

Answer 1

TLDR;

USE [YOUR DATABASE NAME HERE];
GRANT EXEC ON dbo.[YOUR OBJECT NAME HERE] TO PUBLIC

---

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:

/* 
Identifiers used as specified in the question's error message:
zzzzzzz is the database
xxxxxxx is the object
dbo is the schema
*/

USE zzzzzzz;
GRANT EXEC ON dbo.xxxxxxx TO PUBLIC

Answer 2

You can give everybody execute permission:

GRANT Execute on [dbo].your_object to [public]

"Public" is the default database role that all users are a member of.

Not recommended for a production environment, but if you don't care about security, perhaps in a temporary database you're developing with, this will get you going.

Answer 3

As Ganesh stated above, I was missing a "GO" after the end of my SP. I guess the error description provided by microsoft is not accurate enough hehe.

Answer 4

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

Answer 5

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.

Answer 6

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.

Answer 7

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!

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

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.

Answer 12

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

Answer 13

you need to run something like this

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

Answer 14

If you have issues like the question ask above regarding the exception thrown when the solution is executed, the problem is permission, not properly granted to the users of that group to access the database/stored procedure. All you need do is to do something like what i have below, replacing mine with your database name, stored procedures (function)and the type of permission or role or who you are granting the access to.

USE [StableEmployee]
GO
GRANT EXEC ON dbo.GetAllEmployees TO PUBLIC

/****** Object: StoredProcedure [dbo].[GetAllEmployees] Script Date: 01/27/2016 16:27:27 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[GetAllEmployees]
as
Begin
Select EmployeeId, Name, Gender, City, DepartmentId
From tblEmployee

End

Answer 15

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

Answer 16

If you make this user especial for a specific database, then maybe you do not set it as db_owner in "user mapping" of properties

Answer 17

I have faced the same problem and I solved as give db_owner permission too to the Database user.

Answer 18

You don't have the right to execute it, although you have enough permissions to create it.

For more information, see GRANT Object Permissions (Transact-SQL)