I ran into an interesting issue where all DDL and DML statements were being denied on my SSISDB, but specifically on my Internal schema. Here’s a few example error messages I saw:
Description: The SELECT permission was denied on the object ‘master_properties’, database ‘SSISDB’, schema ‘internal’. The UPDATE permission was denied on the object ‘operations’, database ‘SSISDB’, schema ‘internal’. The EXECUTE permission was denied on the object ‘insert_operation’, database ‘SSISDB’, schema ‘internal’.
The SELECT permission was denied on the object ‘current_user_readable_projects’
I’m not sure when or how this issue came to be, but I do know how I solved it!
Resolution:
Make sure dbo owns schema Catalog and also owns schema Internal.
Both schemas were owned by another user who was not ‘dbo’.
What else did I try? Just about everything.
I changed from local system account running the SQL Agent to a known domain sys admin user. I don’t recall the exact error, but it gave an error stating the user needed to be a member of the sysadmin group or I needed a proxy account.
When I added the user to Sysadmin or created a proxy account, the original errors returned.
I double checked the deny permissions, those were not set.
At one point I started to receive this error:
Failed to execute IS server package because of error 0x80131904.
Description: The database owner SID recorded in the master database differs from the database owner SID recorded in database ‘SSISDB’. You should correct this situation by resetting the owner of database ‘SSISDB’ using the ALTER AUTHORIZATION statement.
I set Master and SSISDB to have the same owner here and still saw the same error afterward.
Here’s the URL that helped me the most to resolve the problem:
I have added in a form for feedback, it would be very helpful if you would take a few minutes to fill this out. My goal is to adjust the content to best help others resolve their problems.