Dynamics GP SQL Security

I had the chance to shake hands with Beat Bucher (pronounced: B@) at GPUG Summit 2016 last week, glad to finally make the acquaintance. He's such a faithful contributor to the community.

At any rate, he's posted a SQL Security script on the GPUG user forum this morning and I'll re-blog it here so that it's always available to me. The query will show you the Dynamics users for a company and if the user is using the SQL 'Enforce Password' policy and the 'Expiration' policy. Great auditing tool.

The output looks like this

 

Related Articles

... and you 'll find more on the SQL (Dynamics) Menu

Beat asked me to add a disclaimer that he didn't know the original source of the query to give proper credit...

SELECT
        U.USERID,
        U.USERNAME,
        l.name [sqlname],
        CASE is_policy_checked
            WHEN 1
            THEN 'ENABLED'
            ELSE 'disabled'
            END AS Policy_Checked,
        CASE is_expiration_checked
            WHEN 1
            THEN 'ENABLED'
            ELSE 'disabled'
            END AS Expiration_Checked,
        LOGINPROPERTY([name], 'PasswordLastSetTime') AS [PasswordLastResetDT]
    FROM sys.sql_logins l
        JOIN DYNAMICS..SY01400 U ON l.name = U.USERID
    ORDER BY name;

 

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences