catalogsdeploymentsql serverssis
I am getting below error while deploying package to SSIS catalog on the last step, I am sysadmin and have access to the below views. Can someone help please. Let me know if you need more info. Any help is appreciated
The SELECT permission was denied on the object 'database_principals',
database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server,
Error: 229)
I would try scripting out the operation instead of running whatever Management Studio is doing "for" you. You shouldn't need to use xp_fileexist (assuming you can validate that the files actually exist in the place the script says). Using 2012 SSMS, I am restoring to a point in time that does not coincide directly with a log backup:
The resulting script in my case was:
BACKUP LOG ... WITH NOFORMAT, NOINIT, NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY
RESTORE DATABASE ... WITH FILE = 1, NORECOVERY, NOUNLOAD
RESTORE LOG ... WITH FILE = 1, NORECOVERY, NOUNLOAD
...
RESTORE LOG ... FROM DISK = [...path from line 1...] WITH NOUNLOAD, STOPAT = <time>
I could not resolve this issue and changed my approach to utilize a SQL Job instead of running the package directly from a powershell script.
Summary of solution:
To run a SQL Job with a custom you must also create:
I still wonder what's different about this approach versus the 'interactive' approach listed above. Both approaches run with the exact same identity and privileges!
For an in-depth description of the steps required to configure a SQL Job, look at this example code for Automated Deployment of a SQL Job and Job Step.
Hope this helps someone else in the same situation -
Best Answer
The issue may occur if the user has Deny privilege on the target objects as well. Please check the permissions settings on the target database.
You should remove this roles from your account.