Erroneously getting "Text was encrypted" on a compare

MikeWoods

To preface this, I want to note that I have had a co-worker run my exact same project and it results in all objects being identical (which is the expected result since the databases should be the same).

Anyways, I'm running 8.1.0.360 and when I compare these two databases I'm getting a lot of tables being identified as different. All table differences seem to be be showing the same kind of differences. Here is one such example:

Database A:
[HasBeenPrinted] [bit] NOT NULL CONSTRAINT [DF_ReportFI_HasBeenPrinted] DEFAULT (0),

Database B:
[HasBeenPrinted] [bit] NOT NULL CONSTRAINT [DF_ReportFI_HasBeenPrinted] DEFAULT -- Text was encrypted,


It seems to be pulling the schema correctly for Database A but it's giving that "Text was encrypted" comment on Database B. Like I said above, I know for sure that these databases have the same schema so this seems to be false positives. Also, since a co-worker has run this exact same project and and gets a return of identical schemas it leads me to believe that we can rule out Ignore, and Behavior options as a possible culprit.

Anyone have ideas on what could be causing all of the differences to be showing up when I run it as opposed to when someone else runs it?

Michelle T

False 'text was encrypted' on a DEFAULT normally means that for some reason you can't read the sys.syscomments entry for that default. As another co-worker can read the tables fine, that sounds like a permissions issue (rather than a sys.syscomments table corruption issue, the other common cause). Can you check that you have the same permissions as your co-worker?

MikeWoods

That was just the information I needed. I changed my permissions from datareader to dbowner and it works just fine.

Thanks so much, Michelle.