June 16, 2011 at 6:09 am
Hi Orlando,
We have now retried the changes in production enviroment - doing one step at a time and testing the application afterwards.
As soon as we changed ownership of schema A in database DBY and regranted the permissions, the application got the described security error again.
I checked ownership of all the objects involved - every object had owner = dbo.
We then tried to do the ALTER VIEW (without any regrants) as our first attempt to fix the problem -
and it solved it 😀
Our next attempt would have been to stop/start SQL server - but we never got that far.
Maybe stop/start would also have solved the problem.
I believe that our problem was caused by a SQL server bug.
Maybe some sort of cached ownership information regarding the view wasn't cleared/invalidated when we changed ownership of the schema.
Thanks for ur help.
Best regards
Carsten
June 16, 2011 at 9:34 am
carsten.jorgensen (6/16/2011)
Hi Orlando,We have now retried the changes in production enviroment - doing one step at a time and testing the application afterwards.
As soon as we changed ownership of schema A in database DBY and regranted the permissions, the application got the described security error again.
I checked ownership of all the objects involved - every object had owner = dbo.
We then tried to do the ALTER VIEW (without any regrants) as our first attempt to fix the problem -
and it solved it 😀
:laugh:
Our next attempt would have been to stop/start SQL server - but we never got that far.
Maybe stop/start would also have solved the problem.
I believe that our problem was caused by a SQL server bug.
It's possible it's a bug. Did you involve MS Support?
Maybe some sort of cached ownership information regarding the view wasn't cleared/invalidated when we changed ownership of the schema.
One more theory: it could have something to do with the data access method from the application and that would explain why it worked for you from SSMS post-deployment in the first attempt. Depending on the ODBC driver and how connections are managed (connection pooling? direct connections maintained one-per-user?) I could see where a connection would need to be explicitly reset to pickup a change like that. Recompiling the VIEW could have forced a similar negation of cached permissions that a connection reset would have forced from the client side...just thinking out loud here, I am happy you got it sorted!
Thanks for ur help.
My pleasure! I am not sure I was anything more than a sounding board for you. I appreciate the dialogue, I definitely learned something new.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply