Page History: Grant Execute Permission to Everyone - SQL Server

Compare Page Revisions

4 3 2 1 0 Current 4 3 2 1 0

« Older Revision - Back to Page History - Newer Revision »

Page Revision: Sat, Oct 25, 2008, 11:26 PM

When executed against the database of interest, the following T-SQL code will generate a set of SQL statements which, when executed against the same database, will grant execute permission to public on every function and stored procedure in the database..

SQL Server 2000¶

select distinct 
    sql = 'grant execute on [' + u.name + '].['  + o.name + '] to public'

from 
    sysobjects o

    inner join sysusers u 
        on o.uid = u.uid

where 1=1
    and o.type in ('FN','P')
    and o.name not like 'dt_%'

order by 
    1

SQL Server 2005¶

select distinct 
    sql = 'grant execute on [' + s.name + '].['  + o.name + '] to public' 

from 
    sys.objects o

    inner join sys.schemas s 
        on o.schema_id = s.schema_id

where 1=1
    and o.type in ('FN','P')
    and o.name not like 'dt_%'

order by 1