Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

convert 2000 db to 2008 r2 db Expand / Collapse
Author
Message
Posted Tuesday, March 05, 2013 11:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Tuesday, March 05, 2013 12:15 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:48 AM
Points: 1,264, Visits: 2,393
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
Post #1426979
Posted Tuesday, March 05, 2013 12:28 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Tuesday, March 05, 2013 12:45 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:48 AM
Points: 1,264, Visits: 2,393
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
Post #1426998
Posted Tuesday, March 05, 2013 12:51 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 9:32 AM
Points: 458, Visits: 810
What the the default schema for the user and stored procedure?
Post #1427003
Posted Tuesday, March 05, 2013 12:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Tuesday, March 05, 2013 1:37 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 9:32 AM
Points: 458, Visits: 810
Are you using a fully qualified name (database.schema.object) when you're calling the procedure?
Post #1427030
Posted Tuesday, March 05, 2013 3:43 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:29 PM
Points: 1,744, Visits: 2,549
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)
I'm not fat, I'm gravity challenged.
Post #1427066
Posted Wednesday, March 06, 2013 6:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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 »

Add to briefcase

Permissions Expand / Collapse