Definition of Object Has Changed Since it was last compiles

All I'm having a weird problem.. I have 2 stored procedures that run 98% of the time without any issue but inconsistenly through the following error.

'The definition of object 'proc name goes here' has changed since it was compiled'

We have adding 'with recompile' to the proc but we still get this error - but not consistently. The stored proc is not changing nor is the table structure of any of the objects that are being used in the sp. Any idea to trace down the why this is happening or what objecte it thinks is changing? Let me know your thoughts.

Ken

We've started experiencing this, except:
- It's occurring 100% of the time on SQL 2K5, for a particular data set, but not for another data set on the same schema.
- It never occurred in SQL 2K.
- It only occurs on SQL 2K5 (w/ DB in 2K compatibility mode)

My first reaction, for our case, is that it's a broken 2K5/2K compatibility issue. We're doing something pretty shady - disabling a trigger on table B from within a trigger firing on table A. IOW:

Trigger A, Table A:
- Disable trigger B on table B
- UPDATE table B
- Re-enable trigger B

So I'm guessing SQL 2K5 is finally calling us out on this. But I'd still prefer a quick fix to rewriting the triggers. Have you had any luck with your issue?

default user for object, sysadmin role, KB22067, invalid object

Trying to select rows from a table with a particular owner
which has unfortunately been set to the sysadmin role for
sqlserver by some predecessor.
'select * from mytable' gives error message 'invalid object'
This is an inconvenience
because we have that owner name hardcoded in an unknown
number of places in the code, and many other places queries are
coded in without any owner reference.
In reference to KB255067 it gives two unrealistic workarounds
* don't set the owner in that role
(Waiting for Microsoft TimeMachine(TM) to come out)
* create all the tables with DBO owner
My colleague has invented a workaround where we set the
sysusers.sid to the user we want it to be instead of to 0x01
but I am unsure of the consequences to that.
I would like to know what a truly useable workaround is
that will not have weird consequences later on so that
SqlServer will do what I want it to do.
For example a real workaround would be
* Mark up that user so the evidence of it having been in
the sysadmin role is removed
or in the wording of the KB article
* reconstruct or fix the ownership chain (since it was broken
when the sysadmin role was used)
DO NOT MODIFY SYSTEM TABLES DIRECTLY! Yes, a "qualified" dba could "get
away" with modifying system tables, but merely changing the owner sid is
insufficient as it will not modify the sysdepends table references.
Use sp_changeobjectowner and modify the owner of the object to dbo. If the
old owner was qualified and hard-coded, then map the application login, or
another login, to an aliased user to that old owner name.
A final consideration would be to script out all of those objects, export
the data, drop the objects, and then recreate the objects under a legitimate
database user (note, not login). Then, reimport the data.
Sincerely,
Anthony Thomas

"ElvinKee" <ElvinKee@.discussions.microsoft.com> wrote in message
news:D09D8C22-AD0D-4DBD-81DC-42EACF0CC07E@.microsoft.com...
> Trying to select rows from a table with a particular owner
> which has unfortunately been set to the sysadmin role for
> sqlserver by some predecessor.
> 'select * from mytable' gives error message 'invalid object'
> This is an inconvenience
> because we have that owner name hardcoded in an unknown
> number of places in the code, and many other places queries are
> coded in without any owner reference.
> In reference to KB255067 it gives two unrealistic workarounds
> * don't set the owner in that role
> (Waiting for Microsoft TimeMachine(TM) to come out)
> * create all the tables with DBO owner
> My colleague has invented a workaround where we set the
> sysusers.sid to the user we want it to be instead of to 0x01
> but I am unsure of the consequences to that.
> I would like to know what a truly useable workaround is
> that will not have weird consequences later on so that
> SqlServer will do what I want it to do.
> For example a real workaround would be
> * Mark up that user so the evidence of it having been in
> the sysadmin role is removed
> or in the wording of the KB article
> * reconstruct or fix the ownership chain (since it was broken
> when the sysadmin role was used)
>
>
> --

default user for object, sysadmin role, KB22067, invalid object

Trying to select rows from a table with a particular owner
which has unfortunately been set to the sysadmin role for
sqlserver by some predecessor.
'select * from mytable' gives error message 'invalid object'
This is an inconvenience
because we have that owner name hardcoded in an unknown
number of places in the code, and many other places queries are
coded in without any owner reference.
In reference to KB255067 it gives two unrealistic workarounds
* don't set the owner in that role
(Waiting for Microsoft TimeMachine(TM) to come out)
* create all the tables with DBO owner
My colleague has invented a workaround where we set the
sysusers.sid to the user we want it to be instead of to 0x01
but I am unsure of the consequences to that.
I would like to know what a truly useable workaround is
that will not have weird consequences later on so that
SqlServer will do what I want it to do.
For example a real workaround would be
* Mark up that user so the evidence of it having been in
the sysadmin role is removed
or in the wording of the KB article
* reconstruct or fix the ownership chain (since it was broken
when the sysadmin role was used)DO NOT MODIFY SYSTEM TABLES DIRECTLY! Yes, a "qualified" dba could "get
away" with modifying system tables, but merely changing the owner sid is
insufficient as it will not modify the sysdepends table references.
Use sp_changeobjectowner and modify the owner of the object to dbo. If the
old owner was qualified and hard-coded, then map the application login, or
another login, to an aliased user to that old owner name.
A final consideration would be to script out all of those objects, export
the data, drop the objects, and then recreate the objects under a legitimate
database user (note, not login). Then, reimport the data.
Sincerely,
Anthony Thomas
"ElvinKee" <ElvinKee@.discussions.microsoft.com> wrote in message
news:D09D8C22-AD0D-4DBD-81DC-42EACF0CC07E@.microsoft.com...
> Trying to select rows from a table with a particular owner
> which has unfortunately been set to the sysadmin role for
> sqlserver by some predecessor.
> 'select * from mytable' gives error message 'invalid object'
> This is an inconvenience
> because we have that owner name hardcoded in an unknown
> number of places in the code, and many other places queries are
> coded in without any owner reference.
> In reference to KB255067 it gives two unrealistic workarounds
> * don't set the owner in that role
> (Waiting for Microsoft TimeMachine(TM) to come out)
> * create all the tables with DBO owner
> My colleague has invented a workaround where we set the
> sysusers.sid to the user we want it to be instead of to 0x01
> but I am unsure of the consequences to that.
> I would like to know what a truly useable workaround is
> that will not have weird consequences later on so that
> SqlServer will do what I want it to do.
> For example a real workaround would be
> * Mark up that user so the evidence of it having been in
> the sysadmin role is removed
> or in the wording of the KB article
> * reconstruct or fix the ownership chain (since it was broken
> when the sysadmin role was used)
>
>
> --