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

Grant Execute Permission to Everyone - SQL Server

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 Procedures and Functions Not Granted Execute Rights¶

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. you can also use this script to see what object a given user cannot execute.

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

Grant Execute Rights to a Schema¶

As an alternative to granting execute rights directly to stored procedures, you can grant execute rights to a schema. Thus the user is granted execute rights to any stored procedures in the schema — including those added later. Here's the syntax.

grant execute on schema::dbo to MyUserName