We took a backup of our PROD database and restored it in our Integration testing environment, and ran a script to alter the schema from the current PROD schema to the new schema we wanted to test in Integration testing.
This same script has worked fine in two other environments and exhibited no issues.
However, on our SQL cluster in the Integration testing environment, the script (which is hundreds of lines long and performs numerous things that require you to be dbo to do) fails on one line, a basic UPDATE statement on a single table. The table
has no deny permissions, and only has explicit grant permissions for the Application Role that accesses it. Other users who are dbo can UPDATE the table. I can UPDATE all other tables and do everything else a dbo should be able to do. On
every other SQL instance we have tried this on, it works.
For some reason, this error only occurs specifically on this one instance of SQL, with my specific user, and this specific table. I am baffled as to what could be going on, but need to get this repaired to bring our Integration testing environment
back on line.
What could be causing this?The Pragmatic TSQL Programmer
View Complete Post