Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Grant Permissions Expand / Collapse
Author
Message
Posted Wednesday, August 28, 2013 3:05 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, February 27, 2014 10:41 AM
Points: 361, Visits: 1,781
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%'
Post #1489426
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse