Solved

SQL 2005 Parameterized Views ?????

Posted on 2006-11-14
4
1,509 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

912 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

20 Experts available now in Live!

Get 1:1 Help Now