March 5, 2013 at 11:06 am
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
March 5, 2013 at 12:28 pm
it is a user defined SP!!
March 5, 2013 at 12:45 pm
March 5, 2013 at 12:51 pm
What the the default schema for the user and stored procedure?
March 5, 2013 at 12:55 pm
the stored procedure schema is dbo
the user AA and its default schema is AA
March 5, 2013 at 1:37 pm
Are you using a fully qualified name (database.schema.object) when you're calling the procedure?
March 5, 2013 at 3:43 pm
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
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy