How to determine Power User from SQL

(0) Share

Report

Posted on by L Vail

65,271

Hi all,

I know this is possible because my personal hero, Victoria Yudin, has done it. Well, I've gone a little brain dead tonight and my query is not working as I meant to design it :).

So here's my question, what is the appropriate SQL query that will return a list of folks who are Power Users in Dynamics GP security? I will probably be embarrassed by the simplicity of the answer, but I'll accept embarrassment for simplicity!

I hope to wake up later today with the answer waiting for me. Bless you all!

Leslie

*This post is locked for comments

All responses (3)

Answers (1)

Community Member Microsoft Employee on at

Like (0)

Report

RE: How to determine Power User from SQL

This minor mod adds the user name:

select a.USERID [User ID], c.USERNAME [User name], b.CMPNYNAM Company

from DYNAMICS..SY10500 a

inner join DYNAMICS..SY01500 b

on a.CMPANYID = b.CMPANYID

inner join DYNAMICS..SY01400 c

on a.USERID = c.USERID

where SECURITYROLEID = 'POWERUSER'
L Vail 65,271 on at

Like (0)

Report

RE: How to determine Power User from SQL

Thank you Victoria! You're the best,

These are the same tables I'm using (these and about 6 more), it's good to see there isn't some secret field stored in some other table that I've missed. Now I know I have a join problem somewhere!

Thanks again,

Leslie
Verified answer

Victoria Yudin 22,766 on at

Like (0)

Report

RE: How to determine Power User from SQL

Leslie,

You are too kind. :-) Here is a query that will give you the user ID / company combinations that have the POWERUSER role:

select a.USERID [User ID], b.CMPNYNAM Company

from DYNAMICS..SY10500 a

inner join DYNAMICS..SY01500 b

on a.CMPANYID = b.CMPANYID

where SECURITYROLEID = 'POWERUSER'

Let me know if you need anything else on this.