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>Securables>Add>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
from INFORMATION_SCHEMA.ROUTINES
where routine_type = ‘procedure’
and specific_name NOT LIKE ‘dt_%’ open crsGrant
fetch next from crsgrant into @procname
while @@fetch_status = 0
begin
set @GrantStatement = ‘grant exec on ‘+ @procname +’ to YourRoleName’
exec (@GrantStatement) fetch next from crsgrant into @procname
end
close crsGrant
deallocate crsGrant
Use the following query in your curosor to execlude system procedures… select specific_name
from INFORMATION_SCHEMA.ROUTINES
where routine_type = ‘procedure’
and objectproperty(object_id(specific_name), ‘ismsshipped’) = 0
MohammedU.
Moderator
SQL-Server-Performance.com
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=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</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
http://www.SQL-Server-Performance.Com
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.
]]>