SQL 2000 to SQL 2005 Compatibility mode - application role Issue

We are currently run into an issue with upgrading our production db in
the lab to SQL2005 compatibility mode 9.

The upgrade process:
1. Detached and copied production db to our test SQL2005 server
2. Attached PROD_COPY_DB to sql2005
3. Ran the following script
USE PROD_COPY_DB
GO
SP_UPDATESTATS
GO
4. Did a backup/restore of PROD_COPY_DB to PROD_COPY_DB_05
5. Changed PROD_COPY_DB_05 Compatibility mode from 8 to 9

So I have 2 test DBs on SQL2005:
-PROD_COPY_DB       --> Compatibility mode 8 (2000)
-PROD_COPY_DB_05  --> Compatibility mode 9 (2005)

Running our application: with PROD_COPY_DB
* When we run our application with PROD_COPY_DB we have no issues - it
ran oh so happy.

Running our application: with PROD_COPY_DB_05
* When we run our application with PROD_COPY_DB_05 we get permission
issues.
* My initial reaction is that it seems that Comp_mode_9 application
roles do not function the way they used to.

I am at a loss as how to find the exact cause of the permission issue.


Any insight or suggestions are welcomed.

/Kevin

BooksOnline has an extensive list of the differences between levels 8.0 and
9.0 at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-
48ebca266659.htm
(aka sp_dbcmptlevel).

Also, you can use the Upgrade Advisor at
http://www.microsoft.com/downloads/...&displaylang=en
to find compatibility issues.



--
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com


"Kevinski" <kevin@deepcovelabs.com> wrote in message
news:1143767521.018063.158740@t31g2000cwb.googlegroups.com...
> We are currently run into an issue with upgrading our production db in
> the lab to SQL2005 compatibility mode 9.
>
> The upgrade process:
> 1. Detached and copied production db to our test SQL2005 server
> 2. Attached PROD_COPY_DB to sql2005
> 3. Ran the following script
>          USE PROD_COPY_DB
>          GO
>          SP_UPDATESTATS
>          GO
> 4. Did a backup/restore of PROD_COPY_DB to PROD_COPY_DB_05
> 5. Changed PROD_COPY_DB_05 Compatibility mode from 8 to 9
>
> So I have 2 test DBs on SQL2005:
> -PROD_COPY_DB       --> Compatibility mode 8 (2000)
> -PROD_COPY_DB_05  --> Compatibility mode 9 (2005)
>
> Running our application: with PROD_COPY_DB
> * When we run our application with PROD_COPY_DB we have no issues - it
> ran oh so happy.
>
> Running our application: with PROD_COPY_DB_05
> * When we run our application with PROD_COPY_DB_05 we get permission
> issues.
> * My initial reaction is that it seems that Comp_mode_9 application
> roles do not function the way they used to.
>
> I am at a loss as how to find the exact cause of the permission issue.
>
>
> Any insight or suggestions are welcomed.
>
> /Kevin
>

Hi

Have you tried re-creating your views/procedures/function in a database that
is mode 9?

John

"Kevinski" wrote:

> We are currently run into an issue with upgrading our production db in
> the lab to SQL2005 compatibility mode 9.
>
> The upgrade process:
> 1. Detached and copied production db to our test SQL2005 server
> 2. Attached PROD_COPY_DB to sql2005
> 3. Ran the following script
>           USE PROD_COPY_DB
>           GO
>           SP_UPDATESTATS
>           GO
> 4. Did a backup/restore of PROD_COPY_DB to PROD_COPY_DB_05
> 5. Changed PROD_COPY_DB_05 Compatibility mode from 8 to 9
>
> So I have 2 test DBs on SQL2005:
> -PROD_COPY_DB       --> Compatibility mode 8 (2000)
> -PROD_COPY_DB_05  --> Compatibility mode 9 (2005)
>
> Running our application: with PROD_COPY_DB
> * When we run our application with PROD_COPY_DB we have no issues - it
> ran oh so happy.
>
> Running our application: with PROD_COPY_DB_05
> * When we run our application with PROD_COPY_DB_05 we get permission
> issues.
> * My initial reaction is that it seems that Comp_mode_9 application
> roles do not function the way they used to.
>
> I am at a loss as how to find the exact cause of the permission issue.
>
>
> Any insight or suggestions are welcomed.
>
> /Kevin
>
>