SQL Server Performance

Grant 'EXECUTE' on all the stored procedures, user-defined functions, etc to a DB role in SQL 2K5

Discussion in 'SQL Server 2005 General DBA Questions' started by xiebo2010cx, Jul 15, 2008.

  1. xiebo2010cx Member

    I know in sql 2000, in order to grant 'Execute' permission on all sps, functions and etc to a DB role, we have to first create a cursor, and run the grant statement to each DB object.
    I read an article recently talking about granting 'EXECUTE' on all sps, functions and etc to a DB role, it said SQL 2005 makes EXECUTE grantable at the database scope. Example like we can issue the below script and this will grant execute permissions on all existing stored procedures and scalar functions AND all subsequently created ones within the MyDB to the DB role.
    Use MyDB
    Create Role DB_executor
    Grant EXECUTE to DB_executor
    Now the role DB_executor has EXECUTE permission on all sps, all scalar functions and all subsequently created ones in MyDB.
    I searched a while on SQL 2K5 BOL, trying to find some definitive words regarding this, while I could not find it.
    I post here just want to confirm with you guys about the correctness about this. thanks.
  2. MohammedU New Member

    You can grant the exec to the schema...if all procedures are owned by schema called 'abc' then the following script will grant execute permission to all procs,,
    GRANT EXECUTE ON Schema::abcTO <UserName>
  3. rohit2900 Member

    Try this....
    declare @name varchar(100), @qry varchar(2000)
    declare cursor cursor_temp
    for select name from dbo.sysobjects where OBJECTPROPERTY (id, IsProcedure) = 1
    open cursor_temp
    fetch next from cursor_temp into @name
    while @@fetch_status = 0
    set @qry = ' grant execute on [dbo].[' + @name + ' ] to [ <user_name> ]'
    exec (@qry)
    fetch next from cursor_temp into @name
    close cursor_temp
    deallocate cursor_temp

Share This Page