Grant Permissions

• HBK_4700

  SSCrazy Eights

  Points: 8932

  More actions

  August 28, 2013 at 3:05 pm

  #395389

  Hi, I need to grant exec permissions to some stored procedures(example start with ABC_SP%) to a db role.

  I created below script and added in SSIS package executive task (Basically I added Sequence Container[exec sql task(results to variable)+ foreach loop container(execsql task)])

  Package is executing successfully but when i check permissions are not granted to db role on those sp's. The same script i can run in Management studio and getting the required output. Please advise.

  select

  'GRANT EXECUTE ON [' + SPECIFIC_NAME + '] to [dbRole]' as GrantStmt

  from information_schema.routines

  where (ROUTINE_TYPE = 'PROCEDURE' OR (ROUTINE_TYPE = 'FUNCTION' and DATA_TYPE <> 'TABLE'))

  AND (SPECIFIC_NAME like 'ABCsp_XYZ%' or SPECIFIC_NAME like 'ABCXYZ%%')

  union all

  select

  'GRANT SELECT ON [' + SPECIFIC_NAME + '] to [dbRole]' as GrantStmt

  from information_schema.routines

  where ROUTINE_TYPE = 'FUNCTION'

  AND DATA_TYPE = 'TABLE'

  AND (SPECIFIC_NAME like 'ABCsp_XYZ%' or SPECIFIC_NAME like 'ABCspXYZ%')

  union all

  select

  'GRANT SELECT,INSERT,UPDATE,DELETE ON [' + TABLE_NAME + '] to [dbRole]' as GrantStmt

  from information_schema.TABLES

  where TABLE_NAME like 'ABCsp_XYZ%' or TABLE_NAME like 'ABCXYZ%'