SQLTeam.com | Weblogs | Forums

Grant Execute Permissions on database


#1

Hi We have SSIS package that has 2 tasks. First it applies script that creates SP's Functions and second applies the grant execute permissions on object. So the second task is failing with following error. It looks like temporary object. When SSIS tries to grant exec on this temp object it is failing because it does not exists. I looked in database and found couple of these temp objects in SP's before I figure it out they disappeared. Any idea what are these temp objects and what process it creates? Thanks.

[Execute SQL Task] Error: Executing the query "GRANT EXECUTE ON dbo.HP00011384 TO db_executor;" failed with the following error: "Cannot find the object 'HP00011384', because it does not exist or you do not have permission.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


#2

The error message means what it says, so:

  1. does dbo.HP00011384 exist? What is it? (table, proc, view, etc)
  2. do you have GRANT permission on it?

#3

Very likely, the objects are specific to your database and/or scripts. Right-click on your database server in SSMS object explorer, Reports -> Standard Reports -> Schema change history and see who deleted them.


#4

Hi, had you checked the database and the SP is there with the correct permission?


#5

Thanks James. I checked in schema change. These are temp SP's created and deleted by Application. When SSIS package script out the permissions script these objects exists but when it tried to apply the script after 2 to 3 seconds these objects does not exist. We escalated the issue to application team. We may need to modify the script to avoid temp objects. Thanks.