convert 2000 db to 2008 r2 db

  • 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

  • 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
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • it is a user defined SP!!

  • 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
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

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

  • the stored procedure schema is dbo

    the user AA and its default schema is AA

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

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply