Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Page History: Grant Execute Permission to Everyone - SQL Server

Compare Page Revisions



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


Page Revision: Thu, May 28, 2015, 7:18 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

Show Stored Procedure Not Executable

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 a selected user on every function and stored procedure in the database that they're NOT ALREADY granted execute permission to..

declare @UserName nvarchar(128) = 'MyUser'

;with DbPermissions as (
    select 
         PermissionState = perm.state_desc
        ,PermissionName = perm.[permission_name]
        ,ObjectID = o.[object_id]
        ,PrincipalName = prin.name
    from 
        sys.database_permissions perm
        inner join sys.database_principals prin
            on perm.grantee_principal_id = prin.principal_id
        inner join sys.objects o
            on perm.major_id = o.object_id
    )
select
     SchemaName = s.name
    ,ObjectName = o.name
    ,SqlCode = 'grant execute on [' + s.name + '].[' + o.name + '] to [' + @UserName + ']'
from
    sys.objects o
    inner join sys.schemas s
        on o.schema_id = s.schema_id
    left join DbPermissions p
        on o.object_id = p.ObjectID
        and p.PermissionState = 'GRANT'
        and p.PermissionName = 'EXECUTE'
        and p.PrincipalName = @UserName
where 1=1
    and o.type in ('FN', 'P')
    and p.ObjectID is null
order by
     s.name
    ,o.name

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.