From a recent email:
Yesterday, I extracted two tables from a customer’s DYNAMICS database - SY01500 (User Master) and SY60100 (User-Company Access), so I could show him which users had which access to their various databases (they have several). It took me quite some time to do it and I know there is a script I could enable in Query Analyzer/Management Studio to cut something like this down to nothing at all from a time perspective. I DTS’d the data out into two Excel sheets, did a VLOOKUP on CMPANYID, which is the common field for both, and then sorted the data by USERID. I listed a User ID for each database/company name they had access to as it is pointless to show the customer a CMPANYID and not a company name. I know I could have written some sort of script in SQL to join the tables on this field so I could have skipped the whole Excel piece completely.
Here is the query:
select company.cmpanyid,
company.cmpnynam,
company.interid,
users.userid
from sy01500 company
join sy60100 users on users.cmpanyid = company.cmpanyid
order by 1,4