EXECUTE AS OWNER always shows as EXECUTE AS CALLER

pureblue

Hi,

I have a problem when comparing databases that have managed stored procedures. Some of our stored procedures are catalogued as “EXECUTE AS OWNER”, rather than the default “EXECUTE AS CALLER”.

When running SQL Compare or even SQL Packager, these stored procedures are always scripted and compared as if they were “EXECUTE AS CALLER”. The “EXECUTE AS OWNER” cataloguing is seemingly ignored. This has the obvious problem of not allowing us to reliably compare, synchronise, or package our databases.

Is this an intentional feature? We are running SQL Compare 5.3.0.44 and I have now noticed that there is a version 6 … does anyone know if this is still a problem this version?

Michelle T

This is not intentional - SQL Compare should distinguish EXECUTE AS OWNER from EXECUTE AS CALLER. It appears to do so succesfully in the test database I have here (very simple stored procedures decorated with different EXECUTE AS statements), in both 5.3 and the current version 6.

If you could give us some more details (ideally, a database backup of the troublesome database, or at least the script of the stored procedures causing the problem) I might be able to reproduce the problem and then we will work on fixing it, but I understand that handing out that kind of information isn't always possible.

My email address is michelle.taylor@red-gate.com if you want to send more details privately.

pureblue

Hi Michelle, thanks for your response.

I'm not able to provide a database or script at this time, but it is interesting that you couldn't reproduce it with your test database. It will be worth me creating my own test database, which I would then be able to send to you if I had a problem.

However, some more information for you which could highlight where the problem may be:

- The Managed Assembly was deployed to the main database from visual studio. The EXECUTE AS OWNER statement is run after this as a separate ALTER script.
- SQL Compare is run with the default set of compare options
- I 'know' it is getting the EXECUTE AS incorrect because I am looking at the SQL Differences window and seeing that it incorrectly says EXECUTE AS CALLER

If there is anything else I can tell you, let me know ... I'm not sure when I'll get the time to be able to create a test database.

Thanks.

Michelle T

Sorry, I misread your original post - with a CLR stored procedure, SQL Compare does appear to be misreading the Execute As clause. Thank you very much for reporting this - hopefully we can get it fixed for the upcoming point release.