Accessing Temp Tables

[Karthik Nagaraj]

Hello Graham,

You could try using global temp tables with a "##", in your case it would
be - ##UserAccess.

K

"Graz79" <graham.smith@orchard-systems.co.uk> wrote in message
news:096c01c347ac$dd8bf5b0$a501280a@phx.gbl...

> Have the following stored procedure
> ***
>
> CREATE PROCEDURE spUserAccess
> @UserName varchar(8),
> @FormName varchar(50)
> AS
> CREATE TABLE #UserAccess (FormName varchar(50))
>
> --check if username is admin if so return all rows
> from program security
> IF @UserName = 'adm'
> BEGIN
> INSERT INTO #UserAccess
>
> SELECT DISTINCT FormName
> FROM ProgramSecurity
>
> END
> ELSE
> BEGIN
> --returns all the forms/controls a
> user has access to
> INSERT INTO #UserAccess
>
> SELECT DISTINCT FormName
> FROM OrchardStaffTeams
> O INNER JOIN ProgramSecurity P ON O.Team = P.Team
> WHERE UserName =
> @UserName
> AND P.SecurityLevel <=
> O.SecurityLevel
>
> END
> GO
> ***
>
> This builds a list of all forms the current user has
> security on within a vb application. The stored procedure
> is called from Visual Basic once the user logs in.
>
> The problem is when I come to check this table later in
> the application the table no longer exists even though I
> have not closed the connection to the server. Has anyone
> come across this before. Can anyone suggest an
> alternative? Thanks

[Graham Smith]

Even if I create the table first it is not available when I call the
stored procedure. The front end is in vb and I am using ADO recordsets.

If I use a Global temp table will this not cause problems with multiple
users? Each user who logs in calls the same stored procedure so gets
there on file list for that session.


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Aaron Bertrand - MVP]

Create a real table. Doesn't sound like you need a #temp table at all.



"Graham Smith" <graham.smith@orchard-systems.co.uk> wrote in message
news:euDyiP7RDHA.2480@tk2msftngp13.phx.gbl...

>
> Even if I create the table first it is not available when I call the
> stored procedure. The front end is in vb and I am using ADO recordsets.
>
> If I use a Global temp table will this not cause problems with multiple
> users? Each user who logs in calls the same stored procedure so gets
> there on file list for that session.
>
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!

[Aaron Bertrand - MVP]

> When they login into the vb application it runs this sp that creates a

> table of access rights for that user. If a second user signs in they
> require there own table (i thought temp tables would be the easiest way
> as each session is kept individual)

Why on earth do they each need their own table? How about a table for all
users, with sessionID as a primary key?

[Daniel Johnson]

Take the 100+ user load off the server and have the clients
store the data locally. The application can obtain the access
rights and store them on the client machine (array, xml etc).


"Graham Smith" <graham.smith@orchard-systems.co.uk> wrote in message
news:uLOkHx7RDHA.2188@TK2MSFTNGP10.phx.gbl...

> I have 100+ Users
> When they login into the vb application it runs this sp that creates a
> table of access rights for that user. If a second user signs in they
> require there own table (i thought temp tables would be the easiest way
> as each session is kept individual)
>
> How would I go about creating a "proper" table for each user that could
> be uniquely identified?
>
> Thanks
>
>
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!

[Andrew J. Kelly]

I agree with Aaron, why use a temp table for this. And why recreate this
each time they log in? How often do their permissions change? Usually not
very often and it would be easy enough to rebuild that users security data
when it does. I would opt for a single table for all users with the
security data pre-calculated.

--

Andrew J. Kelly
SQL Server MVP


"Graham Smith" <graham.smith@orchard-systems.co.uk> wrote in message
news:uLOkHx7RDHA.2188@TK2MSFTNGP10.phx.gbl...

> I have 100+ Users
> When they login into the vb application it runs this sp that creates a
> table of access rights for that user. If a second user signs in they
> require there own table (i thought temp tables would be the easiest way
> as each session is kept individual)
>
> How would I go about creating a "proper" table for each user that could
> be uniquely identified?
>
> Thanks
>
>
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!

[Jay Schmitendorf]

It will not create a problem if the user is unique, I am not sure session ID
buys you anything.

....Until they tell you security level is not good enough. That different
organizations
delegate different kinds of permissions to different controls.

If you can stop things mid stride, go look at tempdb and see if the table is
really gone.
It may have a different owner.

You may want to add the user name to the table.

Which is a way to make the global temp
idea work (no coding solution) put the user name in the global table
'##_' + @USERNAME + '_UserAccess'
or
@USERNAME + '.' + '##UserAccess'

"Graham Smith" <graham.smith@orchard-systems.co.uk> wrote in message
news:euDyiP7RDHA.2480@tk2msftngp13.phx.gbl...

>
> Even if I create the table first it is not available when I call the
> stored procedure. The front end is in vb and I am using ADO recordsets.
>
> If I use a Global temp table will this not cause problems with multiple
> users? Each user who logs in calls the same stored procedure so gets
> there on file list for that session.
>
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!