| Author |
SQL 2000 to SQL 2005 Compatibility mode - application role Issue
|
|
| Kevinski 2006-03-30, 8:23 pm |
| 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
| |
| Hal Berenson 2006-03-31, 3:23 am |
| 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
>
| |
| John Bell 2006-03-31, 3:23 am |
| 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
>
>
|
|
|
|