Getting a list of Users with Permission to use an object

lduddridge

Hello all,

I am trying to write a bit of sql which returns a list of user logins who have permission to run a selected object, based the roles they belong to.

I have got so far but have just hit a brick wall. ](*,)
Here is what I have so far:

select * from [Permission] P
JOIN "Windows Access Control" WAC on P."Role ID" = WAC."Role ID"
JOIN "Windows Login" WL on WAC."Login SID" = WL."SID"
where [Object ID] = '[INSERTDESIREDIDHERE]'

Now this would be ok if the User ID were populated, but in what I am seeing for our install it isnt, so all I have is an SID to work with.

Can anyone help me with where to go from here? is there a way to use this data to link in the sysusers table? or is there a different table in Navision that I could link to?

Cheers in advance

L Duddridge

AlexWiley

Well, table #2000000054 - Windows Login stores the corresponding SID and User ID, except you'll need to filter out any server precursors that might exist (Login= COMPANYNAME\UserID). Not sure if that is going to help you or not, but I hope so!

lduddridge

The problem is I am already linking to "windows login" and the population of the User ID field is sparce, randomly some have been added whilst others are blank.

As an example I have a report which 3 people have access to, only one has a User ID filled out and the other 2 are some how linked, but I do not know how.

AlexWiley

Maybe try building it out from the Roles table, since that has the User ID and Role, then do a match that will put all of the permission lines (linked by Role ID) next to each role.

lduddridge

Which table is this?

I've found a "user role" table, but have not found a User ID column in that.

We are running version 3.7 is that any help?

AlexWiley

You'll want to link the Table 2000000053- Windows Access Control with Table 2000000005- Permission. Use the Role ID field to connect the two tables. It might be huge, but it will give you a list of every object that a user ID has access to; maybe consider running a script on it to find/delete repeated object permissions across roles?

lduddridge

ahhhhhh that table has a lookup script:
Lookup("SID - Account ID".ID WHERE (SID=FIELD(Login SID)))

beautiful.

If I cannot access that outside of Navision then I can hopefully stick a report to get the data!

Thanks fella.
\:D/

thetallbloke

Howdy...

Does anyone know what the raw SQL script is that would build the "SID - Acount ID" table...????

Thanks

AlexWiley

http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx