DECLARE @SQLST  [nvarchar](500)
DECLARE @SP_NAME  [nvarchar](80)
DECLARE @VIEW_NAME  [nvarchar](80)


DECLARE storproc_cursor CURSOR FOR  
	SELECT 
	  name
	FROM 
	  sys.objects
	WHERE type='P'
	AND schema_Name(schema_id) = 'puma'   
	ORDER BY name

OPEN storproc_cursor   
FETCH NEXT FROM storproc_cursor INTO @SP_NAME

WHILE @@FETCH_STATUS = 0   
BEGIN   
        
		SET @SQLST = N'GRANT EXECUTE ON [puma].['+RTRIM(@SP_NAME)+'] TO [RunStoredProcedure]'
		PRINT @SQLST
		
		EXECUTE sp_executesql @SQLST;
		
		FETCH NEXT FROM storproc_cursor INTO @SP_NAME
		
END   

CLOSE storproc_cursor   
DEALLOCATE storproc_cursor 

DECLARE storproc_cursor CURSOR FOR  
	SELECT 
	  name
	FROM 
	  sys.objects
	WHERE type='P'
	AND schema_Name(schema_id) = 'hips'   
	ORDER BY name

OPEN storproc_cursor   
FETCH NEXT FROM storproc_cursor INTO @SP_NAME

WHILE @@FETCH_STATUS = 0   
BEGIN   
        
		SET @SQLST = N'GRANT EXECUTE ON [hips].['+RTRIM(@SP_NAME)+'] TO [RunStoredProcedure]'
		PRINT @SQLST
		
		EXECUTE sp_executesql @SQLST;
		
		FETCH NEXT FROM storproc_cursor INTO @SP_NAME
		
END   

CLOSE storproc_cursor   
DEALLOCATE storproc_cursor 


DECLARE mtr_storproc_cursor CURSOR FOR  
	SELECT 
	  name
	FROM 
	  sys.objects
	WHERE type='P'
	AND schema_Name(schema_id) = 'hips'
	AND name LIKE 'Monitoring%'
	ORDER BY name

OPEN mtr_storproc_cursor   
FETCH NEXT FROM mtr_storproc_cursor INTO @SP_NAME

WHILE @@FETCH_STATUS = 0   
BEGIN   
        
		SET @SQLST = N'GRANT EXECUTE ON [hips].['+RTRIM(@SP_NAME)+'] TO [MonitoringRole]'
		PRINT @SQLST
		
		EXECUTE sp_executesql @SQLST;
		
		FETCH NEXT FROM mtr_storproc_cursor INTO @SP_NAME
		
END   

CLOSE mtr_storproc_cursor   
DEALLOCATE mtr_storproc_cursor 

DECLARE mtr_view_cursor CURSOR FOR  
	SELECT 
	  name
	FROM 
	  sys.objects
	WHERE type='V' 
	AND schema_Name(schema_id) = 'hips'
	AND name LIKE 'Monitoring%'
	ORDER BY name

OPEN mtr_view_cursor   
FETCH NEXT FROM mtr_view_cursor INTO @VIEW_NAME

WHILE @@FETCH_STATUS = 0   
BEGIN   
        
		SET @SQLST = N'GRANT SELECT ON [hips].['+RTRIM(@VIEW_NAME)+'] TO [MonitoringRole]'
		PRINT @SQLST
		
		EXECUTE sp_executesql @SQLST;
		
		FETCH NEXT FROM mtr_view_cursor INTO @VIEW_NAME
		
END   

CLOSE mtr_view_cursor   
DEALLOCATE mtr_view_cursor 


