Question

Where are Windows Account details stored in SQL

Administration Laserfiche SDK

Updated September 15, 2014

asked on September 12, 2014

We previously had a requirement to setup a List field that gave a specific user the ability to select a different user from the List based on specific Department listings. Prior to the availability we used static List field types on the LF Template but this was always a problem to keep updated.

Once Dynamic Fields were introduced, we then managed to create a 'View' in a separated SQL DB that pointed to the data listed in the dbo.trustee table. This allowed us to then setup a Dynamic Field that would filter on Group, and make available the users from that group for selection on the Template. This works well as maintenance is so much more simple. By removing the User from the Group when they leave, the User is no longer available for selection on the Template.

This has been running for more than I year now using the Laserfiche Accounts.

We are however in the process to switch over to Windows Accounts and just noticed that the Windows Account is not recorded in the dbo.trustee table. So even though the Windows Account has been added to the respective Group the User details is not available in the trustee table.

Here is a snippet of the code used to create the view:

CREATE VIEW [dbo].[Trustees_ViewDept1]
AS
SELECT     a.trustee_name AS [User], c.trustee_name AS [Group], AFSLaserficheQA.dbo.trustee_attr.attr_name, CAST(AFSLaserficheQA.dbo.trustee_attr.attr_val AS varchar(10)) 
                      AS attr_val
FROM         AFSLaserficheQA.dbo.trustee AS a INNER JOIN
                      AFSLaserficheQA.dbo.grouplist AS b ON b.member_id = a.trustee_id INNER JOIN
                      AFSLaserficheQA.dbo.trustee AS c ON b.group_id = c.trustee_id INNER JOIN
                      AFSLaserficheQA.dbo.trustee_attr ON a.sid = AFSLaserficheQA.dbo.trustee_attr.sid
WHERE     (c.trustee_name <> 'Administrators') AND (c.trustee_name IN ('Customer Admin', 'Procurement', 'Manual Transactions')) AND 
                      (AFSLaserficheQA.dbo.trustee_attr.attr_name = '[Workflow]CompanyID')

GO

Below is screenshots indicating how the Dynamic Field is used for user selection. Initially only the normal Laserfiche Account and then After that once a Windows Account has been added to the Group.

Now after the Windows Account is Added:

We checked the Trustee table and found no trace of the Windows Account which is why we assume out view does not work as expected.

We would therefor Like to know which table contains the Windows Account Info as we would need to amend our View to include the details from there.

the Basis of the view is to give a listing of the Users in respective groups and filtered on a Custom Attribute that is captured on each of the Users. The Sale Attribute is also captured on the Windows accounts, and through Tests in Workflow, we have noticed that the Attribute value resolve correctly.

0 0

Answer

SELECTED ANSWER

replied on September 15, 2014

Thanks for the info Robert.

The only snag we had with that approach is that we did not seem to have a clear way in determining between what is a User vs a Group. We also needed to list the Users per specified set of groups.

We did however manage to get our view working using the below code and this gave us the results we where after:

SELECT     AFSLaserficheQA.dbo.trustee.trustee_name AS [Group], 
           AFSLaserficheQA.dbo.trustee_attr.attr_val AS [User], 
           trustee_attr_1.attr_val AS CompanyID
FROM       AFSLaserficheQA.dbo.trustee 
INNER JOIN
           AFSLaserficheQA.dbo.trusted_group ON AFSLaserficheQA.dbo.trustee.trustee_id = AFSLaserficheQA.dbo.trusted_group.trustee_id 
INNER JOIN
           AFSLaserficheQA.dbo.trustee_attr ON AFSLaserficheQA.dbo.trusted_group.sid = AFSLaserficheQA.dbo.trustee_attr.sid 
INNER JOIN
           AFSLaserficheQA.dbo.trustee_attr AS trustee_attr_1 ON AFSLaserficheQA.dbo.trusted_group.sid = trustee_attr_1.sid

WHERE     (AFSLaserficheQA.dbo.trustee.trustee_name IN ('Customer Admin', 'Procurement', 'Manual Transactions')) 
  AND     (AFSLaserficheQA.dbo.trustee_attr.attr_name = N'[Workflow]DisplayName') 
  AND     (trustee_attr_1.attr_name = N'[Workflow]CompanyID')

We might have to revert to your approach I think in the case where we have a combination of Windows Accounts and Laserfiche Accounts. Provided we can figure out how to link then to their respective Laserfiche Groups.

0 0

replied on September 15, 2014

The account_cache.isuser stores whether the account is a user or a group. Laserfiche doesn't keep the group membership information stored in SQL. To group users by their windows groups, you will need to create a separate table that stores the group membership (and keep it sync'd with the domain).

1 0

replied on September 15, 2014

Hi Robert,

I think I might have not indicated clearly which Groups I was referring to. We do not use the AD Groups at all seeing as the grouping on there is not logical to the functions being performed in Laserfiche. So we have the Normal Laserfiche Groups that we then configure for security and rights. The Windows Accounts are then added to this group.

The SQL Code I shared above however does give the grouping we require so it seems to work well. for our instance.

Thank you for the clarification though on the account cache.

1 0

Replies

replied on September 12, 2014

You can get all known accounts from the account_cache table, which stores the mapping from account name to binary SID. Then you retrieve the trustee attributes from the trustee_attr table by joining on the binary SID. Here is a SQL statement that retrieves all [Workflow]CompanyID attributes for windows accounts except MYDOMAIN\Administrator:

SELECT  c.account_name as [User], a.attr_name, a.attr_val
FROM  account_cache c INNER JOIN trustee_attr a ON c.account_sid=a.sid
WHERE  (c.account_name <> 'MYDOMAIN\ADMINISTRATOR') AND
   (a.attr_name = '[Workflow]CompanyID')

This returns both windows accounts and Laserfiche accounts. If you want to exclude the Laserfiche accounts, you can narrow down the WHERE clause by doing c.account_name LIKE 'MYDOMAIN\%'

1 0

You are not allowed to follow up in this post.

Question

Question

Where are Windows Account details stored in SQL

Answer

Replies

Sign in to reply to this post.