Global Temp Table permissions

I have a VB6 program which has to execute a SQL Server 2005 Stored Procedure
.
The SP creates a global temp table on the data and structure of an input .DB
F
file. I have SYSADMIN rights to the SQL Server. If I run the program, the SP
runs beautifully and creates the GTT. If any of my end users run it, the SP
doesn't give an error, but also doesn't create the GTT. It's got to be a
security issue, but as soon as I take myself off SYSADMIN, I can't create th
e
table, and my users can't create a GTT until I make them a SYSADMIN.
Microsoft can't be serious about this!!!! You can't possibly have to be a
SYSADMIN to create a GTT. Is there anything else I'm overlooking?That does sound strange, but I don't think Microsoft has any constraints lik
e
that. Please verify that you are granting execute premissions to the public
(or any other database role).
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Dave" wrote:

> I have a VB6 program which has to execute a SQL Server 2005 Stored Procedu
re.
> The SP creates a global temp table on the data and structure of an input .
DBF
> file. I have SYSADMIN rights to the SQL Server. If I run the program, the
SP
> runs beautifully and creates the GTT. If any of my end users run it, the S
P
> doesn't give an error, but also doesn't create the GTT. It's got to be a
> security issue, but as soon as I take myself off SYSADMIN, I can't create
the
> table, and my users can't create a GTT until I make them a SYSADMIN.
> Microsoft can't be serious about this!!!! You can't possibly have to be a
> SYSADMIN to create a GTT. Is there anything else I'm overlooking?
>|||Dave
Perhaps the sysadmin account has an appropriate permissions on filesystem.
Can you verify it?
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:433C1E5B-20E8-479D-BD8C-B0DD11CFDFF1@.microsoft.com...
>I have a VB6 program which has to execute a SQL Server 2005 Stored
>Procedure.
> The SP creates a global temp table on the data and structure of an input
> .DBF
> file. I have SYSADMIN rights to the SQL Server. If I run the program, the
> SP
> runs beautifully and creates the GTT. If any of my end users run it, the
> SP
> doesn't give an error, but also doesn't create the GTT. It's got to be a
> security issue, but as soon as I take myself off SYSADMIN, I can't create
> the
> table, and my users can't create a GTT until I make them a SYSADMIN.
> Microsoft can't be serious about this!!!! You can't possibly have to be a
> SYSADMIN to create a GTT. Is there anything else I'm overlooking?
>|||Dave (Dave@.discussions.microsoft.com) writes:
> I have a VB6 program which has to execute a SQL Server 2005 Stored
> Procedure. The SP creates a global temp table on the data and structure
> of an input .DBF file. I have SYSADMIN rights to the SQL Server. If I
> run the program, the SP runs beautifully and creates the GTT. If any of
> my end users run it, the SP doesn't give an error, but also doesn't
> create the GTT. It's got to be a security issue, but as soon as I take
> myself off SYSADMIN, I can't create the table, and my users can't create
> a GTT until I make them a SYSADMIN. Microsoft can't be serious about
> this!!!! You can't possibly have to be a SYSADMIN to create a GTT. Is
> there anything else I'm overlooking?
As you can see from this repro, no particular permissions are needed to
create global temp tables:
CREATE LOGIN hans with password = '//)LD((d'
CREATE USER hans
go
execute as login = 'hans'
go
create table ##temp (a int NOT NULL)
go
revert
go
drop table ##temp
drop user hans
drop login hans
There must be something more that you are not telling us. How do you
know that the global temp table is not created? Could you post the
relevant parts of the code?
An aside, I have rarely found global temp tables to be more a source of
confusing than of any real use. The one exception is when you want to export
data from a stored procedure by calling xp_cmdshell to run BCP.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||We've been progressively adding permissions to various users and groups as
we've gone along. We have Connect and Execute permissions on the database fo
r
all the users (including Guest).
"Mohit K. Gupta" wrote:
[vbcol=seagreen]
> That does sound strange, but I don't think Microsoft has any constraints l
ike
> that. Please verify that you are granting execute premissions to the publ
ic
> (or any other database role).
> Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Dave" wrote:
>|||It turns out we had Adhoc Queries using xBase Drivers disabled in the
Registry for the SQL Server. In the Registry, when we turned
DisallowAdhocAccess off, everything works fine.
Thanks to everyone for your help.
"Dave" wrote:

> I have a VB6 program which has to execute a SQL Server 2005 Stored Procedu
re.
> The SP creates a global temp table on the data and structure of an input .
DBF
> file. I have SYSADMIN rights to the SQL Server. If I run the program, the
SP
> runs beautifully and creates the GTT. If any of my end users run it, the S
P
> doesn't give an error, but also doesn't create the GTT. It's got to be a
> security issue, but as soon as I take myself off SYSADMIN, I can't create
the
> table, and my users can't create a GTT until I make them a SYSADMIN.
> Microsoft can't be serious about this!!!! You can't possibly have to be a
> SYSADMIN to create a GTT. Is there anything else I'm overlooking?
>