web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Table for Security Ope...
Microsoft Dynamics GP (Archived)

Table for Security Operations

(0) ShareShare
ReportReport
Posted on by K Day 7,365

I am trying to get a report that lists the Security Operations that belong to a particular Security Task.  I know the task table is DYNAMICS..SY09000 but that doesn't seem to have a link type field, like a key field, other than the name of the task itself.

Does anyone know what table this sits in?  Basically I would like to recreate a report like the Security Task Setup Report, but not using report writer.  Either a view for Smartlist or an SSRS report.

*This post is locked for comments

I have the same question (0)
  • callen Profile Picture
    callen 4,595 on at
    Re: Table for Security Operations

    There is a very good kb article about security tables.

    Look for article 951229

  • K Day Profile Picture
    K Day 7,365 on at
    Re: Re: Table for Security Operations

    Thanks.  I was looking for that SY09400 table which was listed in that KB Article.

    Now the next question is:  What is with that table where I am getting some nulls by linking the two?  At first glimpse, it seems to not have some 3rd party products listed. But also, there are even some operations that are from GP that seem to be coming back as null.  As if this isn't a complete list.

    Here is a script I am using.  Can anyone else test this to see if they get nulls?

    SELECT
    a.SECURITYTASKID as [Task]
    ,b.PRODNAME as [Product]
    ,b.Series_Name as [Series]
    ,b.TYPESTR as [Type]
    ,b.DSPLNAME as [Display Name]
    ,b.RESTECHNAME as [Technical Name]
    FROM  DYNAMICS..SY10700 as a
    LEFT JOIN DYNAMICS..SY09400 as b on  a.SECURITYID = b.SECURITYID and a.DICTID = b.DICTID and a.SECRESTYPE = b.SECRESTYPE
    ORDER BY a.SECURITYTASKID, b.Series_Name, b.TYPESTR

    I am getting results back from Wennsoft (3rd Party) but not Rockton Auditor or SmartConnect. 

    The thing is that the Actual GP Report pulls the correct information.  Somehow it identifies SmartConnect as SmartConnect and gives all the names of the windows.  But these are coming up a NULL in my query.

    Report Writer is linking SY10700 on a TEMP table where I am linking on the SY09400 .  Which is what I don't know how to recreate..... 

  • L Vail Profile Picture
    L Vail 65,271 on at
    Re: Re: Re: Table for Security Operations

    If you want to know what objects each user has access to, you can run this query:

    SELECT DISTINCT

    S.USERID, S.CMPANYID AS CompanyID, C.CMPNYNAM AS CompanyName, S.SECURITYROLEID, COALESCE (T.SECURITYTASKID, '') AS SecurityTaskID,

    COALESCE (TM.SECURITYTASKNAME, '') AS SecurityTaskName, COALESCE (TM.SECURITYTASKDESC, '') AS SecurityTaskDescription, COALESCE (R.DICTID, '')

    AS DictionaryID, COALESCE (R.PRODNAME, '') AS ProductName, COALESCE (R.TYPESTR, '') AS ResourceType, COALESCE (R.DSPLNAME, '')

    AS ResourceDisplayName, COALESCE (R.RESTECHNAME, '') AS ResourceTechnicalName, COALESCE (R.Series_Name, '') AS ResourceSeries

    FROM dbo.SY10500 AS S LEFT OUTER JOIN

    dbo.SY01500 AS C ON S.CMPANYID = C.CMPANYID LEFT OUTER JOIN

    dbo.SY10600 AS T ON S.SECURITYROLEID = T.SECURITYROLEID LEFT OUTER JOIN

    dbo.SY09000 AS TM ON T.SECURITYTASKID = TM.SECURITYTASKID LEFT OUTER JOIN

    dbo.SY10700 AS O ON T.SECURITYTASKID = O.SECURITYTASKID LEFT OUTER JOIN

    dbo.SY09400 AS R ON R.DICTID = O.DICTID AND O.SECRESTYPE = R.SECRESTYPE AND O.SECURITYID = R.SECURITYID

    In order to get this query to return any results, you must first populate the Security Resource Descriptions table in Dynamics.  you do this by going into the Clear Data screen, change the Display to physical and then insert the Security Resource Description table and then select OK.

    Let us know if this is what you wanted.

     Kind regards,

    Leslie

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans