VIEWs visibility scope

[Albe V°]

We're working on a new release of a software.
In the existing version, each client connects and executes a long batch of
analysis on some tables.

In the new release, we need to allow each client to execute analysis on a
subset of data, according to certain login information.

Our idea is:
1) Connection to db
2) Creation of one View according to restrictions
3) Execution of analysis using just created view and some tables.

The problem is that executing
create view myvista as
select * from tb_data where testo='A1'
from two different clients, creates just one view.

Question: Does it exist a way of creating a VIEW with a connection scope,
something like 'select into ##temptable'?

We tried Create View #MyVista or ##Myvista, but this name is not accepted.

Alternative way, is creating a ##TempTable, at the beginning of the batch,
and using it as data source instead of full table, but, due to really huge
amount of data, this could really disturb server performances.

Thanks for any help

Alberto

[John Bell]

Re: VIEWs visibility scope

Hi

I wouldhope that the same view could be used by each user if you chose the
restriction correctly. Maybe joining to your users table or use of the
IS_MEMBER function will facilitate this.

John

"Albe V°" <vaccariTOGLI_Q UESTO@hotmail.c om> wrote in message
news:sgtQa.1729 75$lK4.4901625@ twister1.libero .it...[color=blue]
> We're working on a new release of a software.
> In the existing version, each client connects and executes a long batch of
> analysis on some tables.
>
> In the new release, we need to allow each client to execute analysis on a
> subset of data, according to certain login information.
>
> Our idea is:
> 1) Connection to db
> 2) Creation of one View according to restrictions
> 3) Execution of analysis using just created view and some tables.
>
> The problem is that executing
> create view myvista as
> select * from tb_data where testo='A1'
> from two different clients, creates just one view.
>
> Question: Does it exist a way of creating a VIEW with a connection scope,
> something like 'select into ##temptable'?
>
> We tried Create View #MyVista or ##Myvista, but this name is not accepted.
>
> Alternative way, is creating a ##TempTable, at the beginning of the batch,
> and using it as data source instead of full table, but, due to really huge
> amount of data, this could really disturb server performances.
>
> Thanks for any help
>
> Alberto
>
>[/color]

[Erland Sommarskog]

Re: VIEWs visibility scope

Albe V° (vaccariTOGLI_Q UESTO@hotmail.c om) writes:[color=blue]
> Our idea is:
> 1) Connection to db
> 2) Creation of one View according to restrictions
> 3) Execution of analysis using just created view and some tables.
>
> The problem is that executing
> create view myvista as
> select * from tb_data where testo='A1'
> from two different clients, creates just one view.
>
> Question: Does it exist a way of creating a VIEW with a connection scope,
> something like 'select into ##temptable'?[/color]

No.

And in any case, if the user is going to create a view, he needs to
have permissions he should not have.

The normal way of doing this is to have a view like:

SELECT * FROM tbl WHERE userid = SYSTEM_USER

Another possibility is to use a table-valued function instead, as this
permits you to pass parameters.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp