Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dhw
Constraint Violating Yak Guru
332 Posts |
 Posted - 2004-09-26 : 16:22:24
|
| Hello...In one of our databases, we have a table for people and another table for their organization info. I have a stored proc now...that is used from our web site that allows users to search for other users. I have a new requirement for a couple of different organizations that do not want anyone outside their organization to be able to search/view their people.I have been trying to take my existing stored proc and modify it for this requirement...but everything I try ends up too complicated. Part of what I was thinking...was adding a new parameter to the stored proc for Organization ID. And then, adding an initial query to the stored proc for looking up the organization id and seeing if this organization has been setup to restrict view/search access (there is a bit column in the Orgs table RestrictOrg which will tell me if this org is restricting data access). Does that make sense...or does anyone have an easier way to manage this sort of behavior.Thanks - dw |
|
|
slacker
Posting Yak Master
115 Posts |
Posted - 2004-09-26 : 17:41:50
|
quote:
[i]Part of what I was thinking...was adding a new parameter to the stored proc for Organization ID. And then, adding an initial query to the stored proc for looking up the organization id and seeing if this organization has been setup to restrict view/search access (there is a bit column in the Orgs table RestrictOrg which will tell me if this org is restricting data access).
I dont think this will work because your restrictOrg BitColumn is essentially saying "This organization only wants its user to have the viewing capability". You have the right idea about adding the the organization id parameter. this is how I would do it.. -- instead of having a search proc this proc will just list all-- users who are viewable by another user. passing the organization-- idea is good because it will eliminate a join step.create procedure ListAllUsers(-- this is the organization of the user who is trying to view.@organizationID int)asselect UserID, UserName, ...FROM UsersINNER JOIN Organizations ON Users.OrganizationID = Organizations.OrganizationIDWHERE (Organizations.OrganizationID = @organizationID OR Organizations.RestrictOrg = 0) AND [ADD YOUR SEARCH CRITERIA] |
 |
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2004-09-26 : 17:57:43
|
| Interesting. Thanks for the suggestion....I'll give it a try and see if the results are what I am looking for. Hopefully it is...because your method looks much simpler than what I was doing.Thanks! - dw |
|
|
|
|
|
|
Restrict Results in SProc By Organization