Navision SID vs. SQL Server SID

vuacorona
vuacorona Member Posts: 23
edited 2007-06-06 in NAV/Navision Classic Client
Hi ALL:

I noticed Navision SID look like this: S-1-5-21-2493456750-3156789078-112345177-1345

and SQL Server SID look like this: 0x0105000000000005150000007A3B9DD049123450BDFBE7A8B0060000

Please advise how to convert Navision SID to match with SQL Server SID.

Thanks
vua

PS: I need to drop some users, and I found them in Navision windows login, but I could not them in SQL server users. This is strange.

Comments

  • vuacorona
    vuacorona Member Posts: 23
    I noticed 22 view and non replied.

    Can someone give little hint ?
  • DenSter
    DenSter Member Posts: 8,307
    That means 22 people looked at it and do not know the answer. If someone knows the answer, they will post it, be patient :mrgreen:
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • NavStudent
    NavStudent Member Posts: 399
    How do you get the sql SID and how where do you get the Navision SID?
    my 2 cents
  • amer
    amer Member Posts: 22
    SIDs can have their binary or string representation.

    Maybe you can try with these two Windows APIs (little C++ prog.):):

    int ConvertSidToStringSid(SID *sid, char **stringsid);
    int ConvertStringSidToSid(char *stringsid, SID **sid);

    Regards,
    Amer
  • vuacorona
    vuacorona Member Posts: 23
    NavStudent wrote:
    How do you get the sql SID and how where do you get the Navision SID?

    To get Navision SID, I query from a table created by Navision called "Window Users"

    To get SQL SID, I query from SQL Server system tables called sysusers


    These two SIDs should be the same, but the problem is SQL Server stored as one format and Navision stored as another format.

    So if we master these SIDs, then we can control users from either Navision, or SQL Server.


    Cheers,
    vua
  • vuacorona
    vuacorona Member Posts: 23
    amer wrote:
    SIDs can have their binary or string representation.

    Maybe you can try with these two Windows APIs (little C++ prog.):):

    int ConvertSidToStringSid(SID *sid, char **stringsid);
    int ConvertStringSidToSid(char *stringsid, SID **sid);

    Regards,
    Amer

    Hi Amer,

    We don't really need to convert it to string because it is already string.

    .
  • DenSter
    DenSter Member Posts: 8,307
    Take a look at the Sessions table, you'll even find idle times there. Delete the session record kicks the user out. This is a virtual table, but i should be available for forms and in code.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • vuacorona
    vuacorona Member Posts: 23
    DenSter wrote:
    Take a look at the Sessions table, you'll even find idle times there. Delete the session record kicks the user out. This is a virtual table, but i should be available for forms and in code.

    Hi DenSter,

    Sessions table and is just a collection of data from SQL Server system tables: sysprocess, sysdatabase, syslockinfo.
  • vuacorona
    vuacorona Member Posts: 23
    Hello All:

    Well ... I am almost get to the solution... [I said almost] ....
    Basically, I query data from table "window login" and get the SID, and write a VB script to find out the login name. Then voila... I got the user name map to the sid. =D>

    Now I found another interesting that is in Navision: click on Tools, Security, Windows Login, I saw a user. But then I could not find this user in the table "windows login"

    So the question is: In Navision when you create a new user, where does it store beside SQL Server system tables? Does Navision store all users in its table, and if so do you know what is the table name?

    thanks
    vua
  • bbrown
    bbrown Member Posts: 3,268
    The SID you see in Navision is from the domain. The SID in SQL is for the SQL Login.
    There are no bugs - only undocumented features.
  • vuacorona
    vuacorona Member Posts: 23
    bbrown wrote:
    The SID you see in Navision is from the domain. The SID in SQL is for the SQL Login.

    HI bbrown,

    The SID in Navision is included domain name and login name. Thanks for your comment.


    Now the question is:

    Where is the data from windows login coming from? Do you know the table name?

    To see the data from windows login, click on Tools -> Security -> Windows login
  • bbrown
    bbrown Member Posts: 3,268
    It's coming from the [windows login] table in the Navision database.
    There are no bugs - only undocumented features.
  • vuacorona
    vuacorona Member Posts: 23
    thank you all. I solved the problem.
  • DenSter
    DenSter Member Posts: 8,307
    vuacorona wrote:
    DenSter wrote:
    Take a look at the Sessions table, you'll even find idle times there. Delete the session record kicks the user out. This is a virtual table, but i should be available for forms and in code.

    Hi DenSter,

    Sessions table and is just a collection of data from SQL Server system tables: sysprocess, sysdatabase, syslockinfo.
    I know what it is. You said, in your first post:
    vuacorona wrote:
    PS: I need to drop some users, and I found them in Navision windows login, but I could not them in SQL server users. This is strange.
    With the sessions table you can accomplish that, without having to match Windows SID and SQL SID. Sometimes people think they need to do very complicated things to accomplish a simple task. If all you need to do is kick out idle users, then all you would need is look at the sessions table. By deleting a record from that table, you can kick a user out of the database.
    Daniel Rimmelzwaan
    RIS Plus, LLC

Categories