Execute Permissions for Stored procedure

Question

Post reply

Execute Permissions for Stored procedure

Raghavender Chavva

SSCrazy Eights

Points: 8669
More actions
July 8, 2011 at 2:51 am

#389935

Hi All,
We have a user for a database. Here we need that user should have exec permissions on all stored procedure.
If any new stored procedure created, that user should automatocally have the exec permission.
How can we acheive this ?
Thank You.
Regards,
Raghavender Chavva

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 1

You could dynamically script the grant execute command and execute it as part of a DDL trigger for the database.

Create A DDL trigger which will detect any create procedure commands

As part of the DDl trigger

create a dynamic sql which will generate and execute something like

declare @sql nvarchar(1000)

set @sql = 'grant execute on '+@procname+' to '+@username+'

exec (@sql)

Jayanth Kurup[/url]

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

Raghavender (7/8/2011)
Hi All,
We have a user for a database. Here we need that user should have exec permissions on all stored procedure.
If any new stored procedure created, that user should automatocally have the exec permission.
How can we acheive this ?

You can grant EXEC at the database level:

GRANT EXEC TO [database_user] ;

Or if that is too much, you can also grant it at the schema level:

GRANT EXEC ON SCHEMA::[schema_name] TO [database_user] ;

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

opc.three (7/11/2011)
Raghavender (7/8/2011)
Hi All,
We have a user for a database. Here we need that user should have exec permissions on all stored procedure.
If any new stored procedure created, that user should automatocally have the exec permission.
How can we acheive this ?
You can grant EXEC at the database level:
GRANT EXEC TO [database_user] ;
Or if that is too much, you can also grant it at the schema level:
GRANT EXEC ON SCHEMA::[schema_name] TO [database_user] ;

Note that granting exec will also allow the user to execute scalar t-sql user-defined functions.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 4

Hi All,
We have a user for a database. Here we need that user should have exec permissions on all stored procedure.
If any new stored procedure created, that user should automatocally have the exec permission.
How can we acheive this ?
Thanks and Regards!!
Raghavender Chavva

First create the script with the help of this query then execute it onece but change the user name in this script,user must have public rights on the required database

Select name,type_desc,'GRANT EXECUTE on ['+name+'] to '+'[<username'++'>] ;' as script from sys.objects

where type = 'P'

then schedule this script in the SQL Server agent job

declare @script varchar(1000)

declare _cursor cursor for

select 'GRANT EXECUTE on ['+name+'] to '+'[testo] ;' as script from sys.objects

where type = 'P' and create_date = GETDATE()-0.1

open _cursor

fetch next from _cursor into @script

while @@FETCH_STATUS = 0

begin

exec @script

fetch next from _cursor into @script

end

close _cursor

deallocate _cursor

Job interval is depend on your requirement,if you have required on the same then @Jayanth_Kurup already provide it

My Blog
www.aureus-salah.com