convert 2000 db to 2008 r2 db

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » convert 2000 db to 2008 r2 db

convert 2000 db to 2008 r2 db

Rate Topic

Display Mode

Topic Options

Author

Message

stephenhai

Posted Tuesday, March 05, 2013 11:06 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 10, 2013 9:46 AM
Points: 25, Visits: 241

Upgrade to 2008r2 from 2000. Backup the 2000 db and restore to 2008 r2. Create all application users and add all the role and right same as 2000.

When I point the appl to new 2008 r2 db, it said my application user does not have 'execute' right to one stored procedure.

But when I point back to 2000 db, everything is OK. The application is up as usual.

I find out that my appl user does not have 'execute' right for that stored procedure too on 2000.

It seems I am missing something. Anything is default in 2000 but I need to grant manually??

Or anything about right or role I do not pay attention when upgrading from 2000 to 2008 r2.

Hope anyone can give me some advices

Thanks a lot

Post #1426944

Robert klimes

Posted Tuesday, March 05, 2013 12:15 PM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:56 AM
Points: 893, Visits: 1,893

Is this a user defined stored proc or a system one?

the user might be dbo or sysadmin on the 2000 server or the proc might have execute permissions granted in the public role.

Bob
-----------------------------------------------------------------------------
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Post #1426979

stephenhai

Posted Tuesday, March 05, 2013 12:28 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 10, 2013 9:46 AM
Points: 25, Visits: 241

it is a user defined SP!!

Post #1426987

Robert klimes

Posted Tuesday, March 05, 2013 12:45 PM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:56 AM
Points: 893, Visits: 1,893

is the user in sysadmin server role on the 2000 box or is the user dbo (not db_owner role) of database on the 2000 box?

Bob
-----------------------------------------------------------------------------
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Post #1426998

bubenik.1

Posted Tuesday, March 05, 2013 12:51 PM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 265, Visits: 577

What the the default schema for the user and stored procedure?

Post #1427003

stephenhai

Posted Tuesday, March 05, 2013 12:55 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 10, 2013 9:46 AM
Points: 25, Visits: 241

the stored procedure schema is dbo
the user AA and its default schema is AA

Post #1427005

bubenik.1

Posted Tuesday, March 05, 2013 1:37 PM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 265, Visits: 577

Are you using a fully qualified name (database.schema.object) when you're calling the procedure?

Post #1427030

ScottPletcher

Posted Tuesday, March 05, 2013 3:43 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 10:11 AM
Points: 1,333, Visits: 1,803

In SQL 2008, if you want a specific user to be able to execute any stored proc (existing ones and newly created ones), you can do this:

USE <your_db_name>

GRANT EXECUTE TO <user_name> --there is no "ON" clause

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson

Post #1427066

stephenhai

Posted Wednesday, March 06, 2013 6:48 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 10, 2013 9:46 AM
Points: 25, Visits: 241

thanks...after grant the execute right to the user, it works now. I think this is good one

Post #1427359

« Prev Topic | Next Topic »

Permissions