Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL 2005 Parameterized Views ?????

Posted on 2006-11-14
4
Medium Priority
?
1,518 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 143

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 2000 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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

916 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