July 22, 2008 at 8:40 am
We recently upgraded a server from SQL 2000 to SQL 2005. On one of our databases, we have several stored procs that call other stored procs. Before the upgrade, we only had to make sure that permissions were assigned to the original stored proc for the user running it...apparently, the permissions would cascade down to procs that were called by the original one.
Since the upgrade, we are having issues where if the appropriate permissions are not set for the procs called by the original proc, no data is returned. We don't get an error or anything...we just get nothing.
Is this a security enhancement in SQL 2005 that we need to compensate for, or is there a switch that can be set to allow the previous functionality?
Thanks
July 22, 2008 at 9:01 am
Are all the procs in the same schema? The easiest way to fix this is to set GRANT Execute On SCHEMA :: [schema_name] to [principal_name].
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 22, 2008 at 9:44 am
When you say other SP's, are these linked through a linled server or do they reside in the SAME Database/Diff Databases..?
you can use GRANT EXECUTE on {SP Name} to {username}
Or if you wish to grant execute on all SPs, use this :
select 'grant execute on '+name+' to [{username}]' from sysobjects where type='P' and Category = 0
But it will be helpful to know the answers to the above questions.
Maninder
www.dbanation.com
July 22, 2008 at 9:49 am
These are all stored procedures in the same database. Granting permissions to each sp or to the whole schema are all well and good, but we were hoping to keep our current security permissions in place. We just weren't expecting this impact from the upgrade.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply