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 |
|
mattyp79
Starting Member
5 Posts |
 Posted - 2008-08-11 : 22:45:02
|
| I have created a stored procedure where i grab a bunch of records from one table and store them in another table so i can modify them. EGSelect * into table2from table1The problem is when a public user runs this stored proc they run into an error stating Invalid object name 'table2'I assume this is a result of their permissionsI am trying to find a way around this without creating the table 1st and inserting all the values in, and i'm not quite sure which permissions to adjust for the public users who will need to use this procI've tried a temp table EGSelect * into #table2from table1But this doesn't seem to work |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-11 : 23:01:40
|
| Didn't understand why you need SP for this? Any reasons to create new tables ? |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-08-11 : 23:12:08
|
| Which schema is table2 in? Same as the sp? If not, you need reference it as schema.table2 in sp. |
|
|
|
mattyp79
Starting Member
5 Posts |
Posted - 2008-08-12 : 01:00:20
|
quote:
Originally posted by sodeep
Didn't understand why you need SP for this? Any reasons to create new tables ?
I need to create a SP as there are input variables and the SP is used by a crystal report. I have created 3 temp tables, 1, i populate with dates and periods the others contain data from various tables which i have modified, I then perform inner and outer joins between the tables. |
|
|
|
mattyp79
Starting Member
5 Posts |
Posted - 2008-08-12 : 01:06:31
|
quote:
Originally posted by rmiao
Which schema is table2 in? Same as the sp? If not, you need reference it as schema.table2 in sp.
It is in the same shema as the sp |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-12 : 01:07:59
|
| can you post your select into script? |
|
|
|
mattyp79
Starting Member
5 Posts |
Posted - 2008-08-12 : 01:18:22
|
quote:
Originally posted by visakh16
can you post your select into script?
|
|
|
|
mattyp79
Starting Member
5 Posts |
Posted - 2008-08-12 : 01:29:26
|
quote:
Originally posted by visakh16
can you post your select into script?
The Query is 5 pages long so i wont put it up here. However run the following and you will get the same error--create procedure A_Test------------------Create procedure A_testasselect * into test_tablefrom <any tablename>select * from test_tabledrop table test_table----------------------------------------------give permissions to public for the proceduregrant all on A_test to publicNow log in as a public user and run the procedureA_testand you will get an error relating to the table 'test_table' |
|
|
|
|
|
|
Table Privileges