Execute prmissions on Stored procedures

Question

Execute prmissions on Stored procedures

klnsuddu

SSCertifiable

Points: 5836
More actions
March 2, 2009 at 2:28 pm

#131418

Hi,
I have created a login and gave the read and write database roles to all databases in an instance.Now, the developer getiing an error like execute permission was denied on object abc in database mydb. So How can I give execute permisson on all stored procedures in all databases?
Should I give any other database role? dbdatareader and dbdatawriter is not enough?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

There isn't a built in database role for EXECUTE permissions. You could script GRANT EXECUTE statements for each database and execute them. Something like this:

select 'GRANT EXECUTE ON ' + name + ' TO youruser' from sys.sysobjects where xtype = 'P'

Greg

klnsuddu SSCertifiable Points: 5836 More actions · Answer 2

thanks,

I executed the select 'GRANT EXECUTE ON ' + name + ' TO youruser' from sys.sysobjects where xtype = 'P'

Even after doing this, the developer getting the same error:

Execute permission was denied on the object abc, database mydb, schema dbo

please advice me what will be permission I need to grant?

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

The query will produce a GRANT statement for each stored procedure in the current database. You have to execute the GRANT statements in a query window to grant the EXECUTE permission to the user. So copy GRANT statements, paste them in a query window and execute them.

Greg

klnsuddu SSCertifiable Points: 5836 More actions · Answer 4

I found one solution as below from the link http://www.sqldbatips.com/showarticle.asp?ID=8

SQL Server 2005 improves on the current situation by making the EXECUTE permission grantable at the database scope. This means that we can issue a statement like the example below and this will GRANT execute permissions on all existing stored procedures and scalar functions AND all subsequently created ones. Thus it acts very much like the current fixed database roles such as db_datareader

/* CREATE A NEW ROLE */

CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */

GRANT EXECUTE TO db_executor

So I need to create this role in each database and grant this role to the the login right?

But there are many stored procedures starting with sys and sp in the schema sys, so how to exclude these procedures if we create a role and grant execute permissons to that role using above method?

Because Iam thinking if follow above method the login will get grant permissions on all stored procedure in that database including system stored procedures right?

please advise me

Greg Charles SSC-Forever Points: 45403 More actions · Answer 5

Thanks for reminding me about granting permissions at the database level. It's not very well documented and I'd forgotten about it.

As for your concern about restricting the stored procedures that it grants permission to, it only grants EXECUTE permission to user stored procedures and those system stored procedures that can be executed by Public. So, I don't think you need to worry about excluding them.

Greg

Jeffrey Williams SSC Guru Points: 90258 More actions · Answer 6

For a simpler method, you can grant execute privileges on a schema. For example:

GRANT EXECUTE ON SCHEMA::dbo TO {role or user};

Now, if someone creates a new procedure - you don't have to explicitly grant access to the procedure as long as the procedure was created in the dbo schema.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs