Using MS SQL Statement for File Cabinet Profile

So in versions of DocuWare where we use MySQL, we've been able to use a SQL Statement so that we can create 1 file cabinet profile to utilize the logged in user and the Groups we created to allow the Profile to be "dynamic" based upon in this case, the Department index field.  So this allows us to setup security that will look at the logged in user, the index field for department, and then determine if that user is in that Group and provide access to those Dept documents.  Does anyone know of a way to accomplish this with MS SQL as I've not been able to get it to work.  Here is the query we use for MySQL:

DEPARTMENT IN (SELECT SUBSTRING_INDEX(name, ':', 1)FROM dwsystem.dwgroup WHERE gid IN (SELECT 

gid FROM dwsystem.DWUserToGroup WHERE uid IN (SELECT uid FROM dwsystem.dwuser WHERE name = 

CURRENTUSERLONGNAME()))AND NAME <> 'Public')


Thanks!

Hi Daryl,

I've done this exact thing.  Try this (I couldn't copy/paste so watch out for typos):

DEPARTMENT IN (SELECT NAME FROM dwsystem.dbo.dwgroup WHERE gid IN (SELECT GID FROM dwsystem.dbo.DWUserToGroup WHERE uid IN (SELECT uid from dwsystem.dbo.dwuser WHERE name - CURRENTUSERLONGNAME())) AND NAME <> 'Public')
 

Hello Daryl,

I have done something similar about a year ago, I can look up my statements over the weekend.

However, the way you are describing does only work for groups. If you would want to check if a user is in a certain role, you need to go a different path, as a user can either be directly in a role, or be in a group which is in a role.
So you actually have the following tables for this:

• DWUser
• DWUserToGroup
• DWGroup
• DWGroupToRole
• DWRole
• DWUserToRole

I will message again when I have found my statements from that project.

Greetings from Germany,
Simon H. Hellmann
DocuWare System Consultant

Daryl,
your statement does not work in MSSQL because there is no such a built-in function "SUBSTRING_INDEX" in MSSQL.
So in your case, just could replace
SUBSTRING_INDEX(name, ':', 1)  from mySQL statement
with 
substring(name,1,charindex(':',name,1)-1) in MSSQL statement

Josef, 

I made the substring change you mentioned above and I get the following error "Incorrect syntax near the keyword 'FROM'.  Incorrect syntax near the keyword 'AND'.  Here is my syntax:

DEPARTMENT IN (substring(name,1,charindex(':',name,1)-1) FROM dwsystem.dbo.dwgroup WHERE gid IN (SELECT 

gid FROM dwsystem.dbo.DWUserToGroup WHERE uid IN (SELECT uid FROM dwsystem.dbo.dwuser WHERE name = 

CURRENTUSERLONGNAME())) AND NAME <> 'Public')

Daryl,

you are missing SELECT before "substring" .

You had SELECT in your original statement, but somehow dropped it in your last

Josef,
Not sure how I missed that, but it's there now, but now I get a new error: "Invalid length of parameter passed to the LEFT of SUBSTRIING function"


DEPARTMENT IN (SELECT substring(name,1,charindex(':',name,1)-1) FROM dwsystem.dbo.dwgroup WHERE gid IN (SELECT 

gid FROM dwsystem.dbo.DWUserToGroup WHERE uid IN (SELECT uid FROM dwsystem.dbo.dwuser WHERE name = 

CURRENTUSERLONGNAME())) AND NAME <> 'Public')

Josef,
Wondering if you know why I might be getting the error with the statement you provided?

Daryl,

The problem here is that if any group name doesn't have a colon in it, the SUBSTRING() command is trying to use a negative number when CHARINDEX() comes up with zero. CHARINDEX() will return zero if it doesn't find the character you are looking for.

So, what are the names of your groups? Find this out by performing the query:
 

SELECT name FROM dwsystem.dbo.dwgroup

I don't have any group names with colons, but all my group names have spaces in them. If I change the colon in your query to a space, the query succeeds.

In the same way you keep "Public" out (ostensibly because it doesn't have a colon), you need to keep out any group name lacking a colon via another condition in the WHERE clause:
 

AND CHARINDEX(':', name, 1) > 0

In fact, since that will inherently keep out "Public" as well, you can just use that for a final query of:
 

DEPARTMENT IN (SELECT SUBSTRING(name, 1, CHARINDEX(' ', name, 1) - 1) AS Department FROM dwsystem.dbo.dwgroup
    WHERE gid IN (SELECT gid FROM dwsystem.dbo.DWUserToGroup WHERE uid IN (SELECT uid FROM dwsystem.dbo.dwuser WHERE name = CURRENTUSERLONGNAME()))
    AND CHARINDEX(':', name, 1) > 0)

As I said, I get no records back (because I have no groups with colons in the name), but at least the query does not error out on SQL Server.

Good luck!
Joe Kaufman

Joe,
I'm using "-" in the Group name (ex:  Dept Head-Aging)....so I did what you suggested and took out the : and replaced with -, however, my SQL query returns zero results...even though I know there's a Doc in there for that user (here's my Statement)

DEPARTMENT IN (SELECT substring(name,1,charindex('-',name,1)-1) FROM dwsystem.dbo.dwgroup WHERE gid IN (SELECT 

gid FROM dwsystem.dbo.DWUserToGroup WHERE uid IN (SELECT uid FROM dwsystem.dbo.dwuser WHERE name = 

'bsikora')) AND NAME <> 'Public')


I've also tried this Statement, which gives me a result in SQL, but gives me the following error "Invalid length parameter passed to the RIGHT function"  when I put it in DocuWare's SQL Statement in File Cabinet Permissions:

DEPARTMENT IN (SELECT right([name], charindex('-', reverse([name])) - 1)

 


FROM dwsystem.dbo.dwgroup WHERE gid IN (SELECT

 

gid FROM dwsystem.dbo.DWUserToGroup WHERE uid IN (SELECT uid FROM dwsystem.dbo.dwuser WHERE (name =

 

CURRENTUSERLONGNAME()))AND NAME <> 'Public')

Daryl,

You are still including the " <> 'Public' in the first statement, which you don't need to. Your statement should instead look like:

DEPARTMENT IN (SELECT SUBSTRING(name, 1, CHARINDEX('-', name, 1) - 1) AS Department FROM dwsystem.dbo.dwgroup
    WHERE gid IN (SELECT gid FROM dwsystem.dbo.DWUserToGroup WHERE uid IN (SELECT uid FROM dwsystem.dbo.dwuser WHERE name = 'bsikora'))
    AND CHARINDEX('-', name, 1) > 0)

If that works, change <'bsikora"> back to <CURRENTUSERLONGNAME()> (without the brackets) and you are all set. You should still always leave the check in there to make sure the group name has a dash, and you should remove the check for group name not being "Public".

If you still get back no data, then your statement is correct, it just either isn't finding that username, or your group names do not actually have dashes in them. Did you get a list of group names with query:
 

SELECT name FROM dwsystem.dbo.dwgroup

and did you verify the group names are what you anticipate? And you are sure that user is in the group you state? I am not sure what else to look for if the SQL query is syntactically correct. I am able to run queries for users in our system and get a list of group names back properly, using a space as my delimiter.  What if you just run the query:
 

SELECT Name AS GroupName FROM dwsystem.dbo.dwgroup
    WHERE gid IN (SELECT gid FROM dwsystem.dbo.DWUserToGroup WHERE uid IN (SELECT uid FROM dwsystem.dbo.dwuser WHERE name = 'bsikora'))

? Do you get back a list of all groups bsikora resides in? Do those group names have the expected dashes in them?

Thanks,
Joe Kaufman