Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
 Posted - 2004-07-22 : 19:31:26
|
| My users are running a web-based search page that hits an sproc. The sproc grabs the form parameters, accesses a base table, and then creates a temp table.I should note that this base table is created by a separate sproc that is launched several times a day via sql agent job. This sproc drops the existing base table and creates a new one.The problem arises when users send the search page. IE error indicates access denied, user does not have permissions to base table.Of course, if I manually give user permissions to base table it all works. I don’t want to do this, however.My question: if the user has exec privileges to the sproc this should mean that no permissions are necessary on the base table, right? Is it possible that the re-creation of the base table via sql agent job several times a day is triggering this behaviour?Thx |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-22 : 19:34:11
|
| Got dynamic SQL in there?EXEC permission on the sproc is enough except in the case of dynamic sql. With dynamic sql, you must grant explicit permissions on the tables in order for it to work as dynamic sql doesn't run in the same context as the sproc.Tara |
 |
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-07-22 : 19:50:54
|
| Tara,you got it. yes on dynamic sql.now the question is: how can I grant explicit permissions within the sproc that creates the base table in the first place?thx |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-22 : 19:52:54
|
| CREATE PROC SomeProcASCREATE TABLE TableName...GRANT SELECT ON dbo.TableName TO RoleNameRETURNGOBut why bother recreating the table every day? Why not just delete/truncate the rows in it?Tara |
|
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-07-22 : 20:59:52
|
| thx Tara.Within the sproc that creates the base table, I need to construct temp tables that ultimately feed into the final product, which the search runs against. I re-create the base table via sproc several times a day to ensure users are seeing the latest data. I chose this scheduled re-create b/c the job is big and takes awhile to complete – doing it on demand for each search request would have users waiting forever for data to return to the screen.I’d rather use a VIEW, of course, but my understanding is that some of the above activity is not permitted in a VIEW. I’m not sure I understand your suggestion, but I’m open to a better solution if you’re willing to elaborate.Thanks again. |
|
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-07-22 : 21:01:30
|
| OH! too late in the day for me ;-)I missed your comment re: delete/truncate.That's the ticket. will do that instead. |
|
|
|
|
|
|
|
|
SELECT denied even though sproc allows it