Check user authorization - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Check user authorization

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-06-16 : 09:25:20

I need to write a sp (for my web app) that checks user authorization. Here is the scenario:

User Authorization Table (AuthTable)

UserID From Cost Center To Cost Center
(UserId) (FromCC) (ToCC)

User1 3000 3600
User1 4800 5120
User1 5200 5250
User2 3000 3400
User2 3760 3840

Production Table (ProdTable) data:

Job Number Cost Center Quantity
(JobId) (JobCC) JobQty

5100 2860 250
5100 3120 525
5100 4801 262
5200 3001 155
5200 5251 276

I need to check the user security for each record that I read from the ProdTable against all entries in the AuthTable for the current user.

When I read the ProdTable and User ID = User1 then selected records should be:

5100/3120, 5100/4801, 5200/3001,

When I read the ProdTable and UserID = User2, then the selected records should be:

5200/3120

I’m not sure how to construct my sql statement. Can somebody give me a hand? Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 10:25:24

[code]SELECT LEFT(rl.resultlist,LEN(rl.resultlist)-1)
FROM
(
SELECT JobId + '/' + JobCC + ',' AS [text()]
FROM ProdTable pt
INNER JOIN AuthTable at
ON pt.JobCC >=FromCC
AND pt.JobCC <=ToCC
WHERE at.UserId=@UserID
FOR XML PATH(''))rl(resultlist)[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources