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

Grant Execute Permission to Everyone - SQL Server

RSS
Modified on Fri, Jul 31, 2015, 5:01 PM by Administrator Categorized as 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

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