Breakdown of Field

[antiskeptic]

This may be impossible and I've tried everything I can think of. But does anyone have a clue how I can do this?

I am working with SQL Server 2000 Query Analyzer
The base table was originally a cvs file that I turned into an Access table...that I am now working with in SQL. MAinly because I exhausted all my options in Access, and hoped SQL would have better luck. But I can change that if it would work better another way.

I have a list of Users and thier permissions to thier respective folders. Each user has permission to a "tree" of folders. But all I care about is the top most "root" folder that they have the same permissions for. And what method works for some, does not work for others when I've tried this so far. So I'll give you a couple of examples:

First, the easy one...

[b]User	  Permissions	FolderPath[/b]
Admins	  all    	Acct\
Admins	  all           Acct\3RD_PRTY\Folder\
Admins	  all           Acct\AR Procedures\
Admins	  all           Acct\AR Procedures\Folder\History

In this one, I would only want to see the very top Acct/ folder. Because this user (Admins) has all permissions to the Acct/ folder, that means he has all permissions to everything inside that folder. So I don't need to see any of the other folder paths inside that folder. Do you see what I mean?

I would only need to see this line:

[b]User	  Permissions	FolderPath[/b]
Admins	  all    	Acct\

In Crystla I used a Instr(FolderPath,"\") to allow me to find out where the first "\" was and chop off that root folder...but I can't seem to get that same ability in SQL. ??????

But for a little harder one that has me really stumped...this is the lay out:

[b]User	  Perms    	FolderPath[/b]
CPAUL	all	Accounting\CO01_OP\Operating 2000\
CPAUL	all	Accounting\CO01_OP\zip files\
CPAUL	all	Accounting\ESCROW\REPLACE\Chasecrossing\
CPAUL	all	Accounting\Unclaimed Funds\
CPAUL	all	Accounting\Unclaimed Funds\6MosOld
CPAUL	all	Accounting\Ready Refunds\
CPAUL	all	Home\CPAUL
CPAUL	all	Home\CPAUL\Favorites

Okay, for this one he only has a couple "root" folders that he has all permissions for. The paths are very similar in the first two records, for example...but he only has access to those two folders, so I would want to see both.

This is what I would want to see from the above example:

[b]User	  Perms    	FolderPath[/b]
CPAUL	all	Accounting\CO01_OP\Operating 2000\
CPAUL	all	Accounting\CO01_OP\zip files\
CPAUL	all	Accounting\ESCROW\REPLACE\Chasecrossing\
CPAUL	all	Accounting\Unclaimed Funds\
CPAUL	all	Accounting\Ready Refunds\
CPAUL	all	Home\CPAUL

I know this is probably not explained very well, but I really tried ...so please ask any questions to help you help me. I've attempted this in Crystal Reports, and Access. And even thought I had it in Crystal with the help of someone in Crystal Forum (here on Tek-Tips)...but it did not work after all. *pout* So I'm trying here to see if some fresh ideas can put me in the right area to get this working.

Just so you know why this is necessary...Managers are needing to see all thier Users' folders and permissions to establish what groups, have what folders / Who is no longer a valid user / etc. It's a cleanup thing, really. But with all the users and folders involved it would take FOREVER...Unless we ca break it down a bit. Any helpful hints would be SOOOO appreciated.

Thank you,
Antiskeptic

 

[pkailas]

try this

SELECT *
FROM

WHERE (CHARINDEX('\', FOLDERPATH) = LEN(FOLDERPATH))

 

[vongrunt]

Maybe this:

select A.*
from blah A
left join blah B on A.FolderPath like B.FolderPath + '%' and A.FolderPath<>B.FolderPath
where B.FolderPath is null

 

[antiskeptic]

pkailas,

Your Idea has the right start...but I need to go beyond the first "/" As in the example below...THere are times that the "Root" folder for that particular user is NOT the root folder of the path. Taken the lay out below:

[b]User   Perms      FolderPath[/b]
MMAson  all    ACQ\AZ\PHOENIX\NEWDEVLP\GIL\	
MMAson	all    ACQ\AZ\PHOENIX\NEWDEVLP\GIL\LOT40W\	
MMAson	all    ACQ\AZ\PHOENIX\NEWDEVLP\GIL\LOT40W\5YR\	
MMAson	all    ACQ\AZ\PHOENIX\NEWDEVLP\GIL\LOT40W\6YR\	
MMAson	all    ACQ\AZ\PHOENIX\NEWDEVLP\GIL\LOT40W\RENTCOMP\
MMAson	all    ACQ\AZ\PHOENIX\NEWDEVLP\INDIAN\

I would only want to pull from this list:

[b]User   Perms      FolderPath[/b]
MMAson  all    ACQ\AZ\PHOENIX\NEWDEVLP\GIL\	
MMAson	all    ACQ\AZ\PHOENIX\NEWDEVLP\INDIAN\

...And IF...just suppose MMason has access to ACQ\AZ\PHOENIX\NEWDEVLP\ then you would only see the one line because the two folders showing, are within \NEWDEVLP\

And vongrunt...
I'm in the midst of trying yours right now...stay tuned. *ha*

Thank you both for the rapid response!

Antiskeptic

 

[antiskeptic]

well, I tried yours vongrunt...but it's just running, and running...it's been running for over 10 minutes with no output. So I don't think that's it either


this is what I used exactly...did I do something wrong?

select A.[User],A.[Permissions],A.FolderPath
from PermissionsByBaseFolder A
left join PermissionsByBaseFolder B 
on A.FolderPath like B.FolderPath + '%' 
and A.FolderPath<>B.FolderPath
where B.FolderPath is null

 

[vongrunt]

Query looks OK... of course I tried it over 8-record table but I really don't see a reason why it runs 10+ minutes. Unless you have 2 million records or something. You can eventually create index over FolderPath and try again.

Btw. I assumed column FolderPath is of type varchar.