Grant execute to all SPs in database automatically | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Grant execute to all SPs in database automatically

Hello again,<br /><br />I am wondering how to give grant execute permissions to a particular account/role automatically, so that when new stored procedures are created, the account will have exec permissions on them WITHOUT BEING DBO.<br /><br />For instance, if I go to account properties&gt;Securables&gt;Add&gt;All objects of types…and chose stored procedures, I still need to manually go through all of the stored procedures and grant execute. And when I create a new procedure, I need to again grant execute.<br /><br />Isn’t there a way to specify "this account will have execute on all procedures in this schema or database" but without making it dbo ?<br /><br />Thanks in advance[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]
I picked this statement and scheduled it according to how often store procedures are created. declare @procname varchar(500)
declare @GrantStatement varchar(2000) declare crsGrant cursor for
select specific_name
where routine_type = ‘procedure’
and specific_name NOT LIKE ‘dt_%’ open crsGrant
fetch next from crsgrant into @procname
while @@fetch_status = 0
set @GrantStatement = ‘grant exec on ‘+ @procname +’ to YourRoleName’
exec (@GrantStatement) fetch next from crsgrant into @procname
close crsGrant
deallocate crsGrant
Use the following query in your curosor to execlude system procedures… select specific_name
where routine_type = ‘procedure’
and objectproperty(object_id(specific_name), ‘ismsshipped’) = 0
In 2005, you can GRANT execute permission on a SCHEMA to a user, so that the user will get permissions to execute all the exisitng and future procedures residing in that particular schema.<br /><br />eg.<br /><br />GRANT EXECUTE ON SCHEMA:<img src=’/community/emoticons/emotion-2.gif’ alt=’:d’ />bo TO myappuser<br /><br /><br /><br />Roji. P. Thomas<br />SQL Server MVP<br /<a target="_blank" href=></a><br />
ITs better to get accustomed to Schemas in this case where you can control such table & object wide permissions. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.