Generate GRANT EXEC script for existing users in the database

>>Script Language and Platform: MS SQL Server
Whenever stored procedures and user defined functions are dropped and recreated in production, it is very tedious to find who had permissions on the dropped procedures and functions before. This script will generate the GRANT EXEC statements for all existing users in the database who have execute permissions. Before dropping the procedures and functions generate this GRANT EXEC code and re-apply once the procedures and functions are re-created.

Author: MAK

set quoted_identifier off
go
select 'grant execute on '+a.name+ ' to ' + user_name(b.uid)  
from sysobjects a ,sysprotects b
where a.type in ('P','Fn') 
and user_name(b.uid)  <>'public' 
and a.name not like 'dt_%'
and a.id=b.id and action=224

Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, or fitness for a particular purpose... Disclaimer Continued

Back to Database Journal Home