Post reply

convert 2000 db to 2008 r2 db

  • March 5, 2013 at 11:06 am

    #290658

    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

  • March 5, 2013 at 12:16 pm

    #1593602

    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]

  • March 5, 2013 at 12:28 pm

    #1593609

    it is a user defined SP!!

  • March 5, 2013 at 12:45 pm

    #1593619

    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]

  • March 5, 2013 at 12:51 pm

    #1593623

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

  • March 5, 2013 at 12:55 pm

    #1593625

    the stored procedure schema is dbo

    the user AA and its default schema is AA

  • March 5, 2013 at 1:37 pm

    #1593645

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

  • March 5, 2013 at 3:43 pm

    #1593674

    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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • March 6, 2013 at 6:48 am

    #1593923

    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