Solved

SQL 2005 Parameterized Views ?????

Posted on 2006-11-14
4
1,507 Views
Last Modified: 2008-01-09
I'm just looking at trying to create a view in SQL 2005 that pulls back data based upon a value.... i.e. :

SELECT     request_item AS [Item Reference], request_by AS [Requested By], requested_on AS [When Requested]
FROM         dbo.PCT_Active_Requests
where request_to = @in_site
---------------------------------------------------------------------------------------------------------------------------------------

Now, I know I can't use parameters in a View, so I'm guessing I need a function. Basically, the 'SITE' argument is calculated on what the user logs into windows as. Again, this is no problem when calling a stored procedure from a .net front end as the system.environment.username is able to be passed as a parameter.

What I'm looking to do, I think....., is to be able to create a view which has a function as the argument for the WHERE statement, but the function needs to be able to know what the user is logged into windows as....we're not planning on using INTEGRATED SECURITY, but if we have to, then we have to....

I hope this makes some sort of sense?

Thanks,

JC
0
Comment
Question by:misdevelopment
  • 2
4 Comments
 

Author Comment

by:misdevelopment
ID: 17938802
Alternatively - is there anything to stop me using a stored procedure instead of a VIEW entirely???

The view / stored procedure is going to be databound to a dataview in a windows forms app...
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17938814
>Now, I know I can't use parameters in a View, so I'm guessing I need a function
correct

you are aware of the function user, system_user ...
0
 

Author Comment

by:misdevelopment
ID: 17938829
If we aren't using integrated security, how will this show me the windows username though?
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 500 total points
ID: 17942723
If you aren't using windows integrated security it won't show the windows user.

I highly recommend using windows integrated security.

You can for example granat a standard windows group SQL Server access.

Then you can still use the SUSER_SNAME() function to return the individual windows login (even they they are granted access through a windows group). When a user needs access to your app you just add them to the windows group (i.e. the standard help desk can do it) and it works automatically. There is no need to add them to SQL Server.

We use this method very successfully here to forever banish the need for users to remeber 93 different logins and passwords for all their different applications (slight exageration)


The handy thing is that you can incorporate this user name into a view, removing the need for passing a paramter in.


For example, if you can tag the request items with the windows user name then create this view:


SELECT     request_item AS [Item Reference], request_by AS [Requested By], requested_on AS [When Requested]
FROM         dbo.PCT_Active_Requests
where request_to_windowsusername = suser_sname()


Then everyone will automatically only see only their own requests when they run this view. There is no need for intermediate logic to capture user names etc.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now