nav-left cat-right
cat-right

SQL query to get companies accessible to a SQL/GP User

You can easily check list of companies accessible to a GP user using Company Access Window. Or you can use a small query on table sy60100

Something like


SELECT *
FROM sy01500 WHERE cmpanyid
IN (SELECT cmpanyid FROM sy60100
WHERE userid = ‘LESSONUSER1′)

However, recently I needed a list of companies a SQL user (not user in GP) had access to. For this I needed list of databases the user had access to and company name corresponding to those databases. This user was created for a reporting application which had limited access on SQL level in a few companies. Here is the query I used to get the list of GP companies for the logged in user


SELECT INTERID, * FROM sy01500
WHERE INTERID IN (SELECT name
FROM sys.sysdatabases
WHERE Has_dbaccess(name) = 1)

Sys.sysdatabases has list of databases on the SQL server. Has_dbaccess tells whether current user has access to a database. At end  I used the database name to get the name of company from SY01500

3 Responses to “SQL query to get companies accessible to a SQL/GP User”

  1. Thanks this post was a greate help for me keep up the good work

  2. Josue Lopez says:

    Thank you, this helped me a lot.

Leave a Reply