EXECUTE permission on stored procedures

Question

EXECUTE permission on stored procedures

FelixG

SSCrazy

Points: 2550
More actions
January 16, 2006 at 8:36 am

#96442

Hi,
I'm running MSSQL 2000 (Though I'm a newbie) and this is my problem:
The following text was extracted from Books on Line:
"EXECUTE permissions for a stored procedure default to the owner of the stored procedure, who can transfer them to other users. Permissions to use the statement(s) within the EXECUTE string are checked at the time EXECUTE is encountered, even if the EXECUTE statement is included within a stored procedure. When a stored procedure is run that executes a string, permissions are checked in the context of the user who executes the procedure, not in the context of the user who created the procedure."
Very clear, isn't it??!!
Since now I'm willing to implement NT integrated security, so I stablished permissions on database objects as "I assume" it's needed, specifically, I granted EXECUTE right on an Stored Proc named spa_GetHistory to the user "User1".
Monthly some user "User2" creates a table containing all the information about the operations of the month. The name of the table is "HistoryYYYYMM", where YYYY is the year and the MM is the actual month.
Under this situation, the user "User1" needs to fetch partial historical data executing the spa_GetHistory stored proc passing the initial and final dates as parameters.
I don't want the User1 to have direct "select" permission on any HistoryYYYYMM, as when any new "user1" is created I need to assign select permisions on all existing HistoryYYYYMMs. By the way, when a new HistoryYYYYMM is created I must set "select" permissions on this table to all user1s.
So... I coded spa_GetHistory, but...... (this is the meat). As the number of tables to consult depends on the initial and final dates supplied, I have to include an EXEC clause that loops through a cursor containing all the table names and selects data from all HistoryYYYYMMs contained in the range and inserting their data into a temp table which is then returned.
At this point when the User1 runs the sp, he gets the error: "SELECT permission denied on object History200501...." because of the :
Exec('Insert Into TempTable select * from History'+@N+'''')
where @N contains the year and month (200501).
Any advice about recoding sp, modifiying security design or any other workaround will be highly appreciated.
Thanks in advance,
Felix

Viewing 5 posts - 1 through 4 (of 4 total)

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

amackay_1 Ten Centuries Points: 1240 More actions · Answer 1

My approach in this situation would be to try to eliminate the addition of tables each month. I would create one master table and add MM and YYYY columns (or a date field), and append new records each month, if possible given any other requirements of the project. That way there would be no need for the temporary table and looping. You could simply select all the records you need from the single table for the date range required.

Andrew

FelixG SSCrazy Points: 2550 More actions · Answer 2

Thanks for your your suggestion... How would I like to have just one and only one table!!!, but all the system is based on these periodical tables, and I'm just trying to enhance the security.

Anyway thank you very much,

Felix

amackay_1 Ten Centuries Points: 1240 More actions · Answer 3

Well, since this is an exising system that cannot be changed to that degree, I would recommend checking out this link: http://support.microsoft.com/default.aspx?scid=KB;EN-US;301299

It explains the reason that you need to grant permissions at the table level when using dynamic sql (creating the query in a variable and using exec to execute it). If you were able to run the query directly within your sp you wouldn't run into this problem, but the dynamic month/year table name foils that idea. The only way I could see to get around it would be to implement a very long (and ugly) If statement for each possible table and then code the full select statement with the table name hard-coded in (with no exec necessary).

Maybe someone else has a better solution?

Ian Yates SSCoach Points: 19738 More actions · Answer 4

Perhaps you could have a stored proc run by user2 that regenerates the stored proc that user1 executes - the dynamically generated stored proc could be aware of the dates for the tables and do the appropriate thing? It would probably need to create a temp table (or table variable) and then insert into that temp table from the appropriate period tables...

This would let user1 execute the proc without needing access to your history tables as user2, who created the dynamic proc that user1 executes, has access to those tables.