This turned out to be a delegation setting in Active Directory.

Thanks Grant for your time and hopefully the thread will help others in the future! It's a weird one.

Good News…

After installing the Service Pack 2 (SQLServer2008R2SP2-KB2630458-x64-ENU.exe), the issue is resolved.

Thanks, I will do that. Nice to know I'm not going crazy.

I should also add that I know the backup is not corrupt because I am able to restore it on a 10.0.5520 instance.

I should add that several other db backups from the same 2008 server DID restore fine on the 2008 R2 instance. Could it be one feature in the failing...

On production (which works), when I view permissions for the given user/database, Explicit tab, I see Grant checked for

Connect

Execute

Insert

Select

Update

The Effective tab shows all of above plus Delete.

The login/user...

Slight correction- actually the new .bak is 500 mb smaller than the original. But even compressed shouldn't a 250 gb reduction in log file size result in more than...

The desire for a single sp is so that a non-sql dev group can run it easily and repeatedly- they need to compare the resulting data when run thru different...

Thanks for the great feedback to all who responded, it's been very helpful. I *fixed* the code using CASE statements, but wrote up options and recommendations for refactoring.

I will try the CASE solution for the one sp that is currently a roadblock. Unfortunately we have many stored procedures (with more on the way) that have this...

Thanks to both of you, much more elegant than the grunt code I had come up with.

m% (wildcard) must match a patient field (such as Michael or Maya)

a% must match a DIFFERENT patient field (such as Ayers or Anne)

a% must match a DIFFERENT patient field...

Agreed, and user validation is done at login time. However we have UserIds assigned to things other than standard users (ie utilities, importing tools) and the UserId values are...

Thanks for the great input. I will make it a point to refactor this code as I come across it.