Database Logins

ta5

Hello
We have a (test) installation of a database that was migrated from native to sql server 2005. In Nav there are no explicit database logins, only windows logins.
However, on the sql server there is a special user (lets call him xy) with server role sysadmin. User xy is able to login with a Nav client to the mentioned database. I sysadmin role is removed, login is not possible anymore for xy.
My question is: Why is this possible? Until now I thought Nav always checks the user rights separatly/additionally ?! :oops:

Hope somebody can shortly describe how it works. Thanks in advance.
Thomas

Shedman

SysAdmins are always allowed into the database under SQL Server. Or maybe you can change the permissions so that he can't. But then again, being SysAdmin on the SQL Server that would be very easy to undo ...

stryk

As Shedman said: sysadmin is granted access to all and everything. Once he logged on to the SQL Server succesfully, no further authentification (e.g. for a specific database) is performed.

If "sysadmin" is not assigned, you need the "3-tier Authentication" with SQL & NAV - you need ...
... a Login on the Server (public); assigned to a ...
... a User in the Database (public)
... and a correspondig Windows or Database Login in NAV; incl. the apropriate NAV roles

Maybe this could also help you: http://dynamicsuser.net/blogs/stryk/archive/2010/02/16/extended-database-hardening-nav-sql.aspx

ta5

Thanks for your valuable details on this topic. Have a good day!
Thomas

ta5

stryk wrote:

Maybe this could also help you: http://dynamicsuser.net/blogs/stryk/archive/2010/02/16/extended-database-hardening-nav-sql.aspx

Hi Jörg
I have studied your blog now. One question to the post: If I understand you correctly, sometimes the public role has explicitly assigned read and write permissions for all or some tables in the nav db. And the permissions are not granted by an admin. Please confirm. Thanks

Thomas

stryk

Well, the problem we encoutered was (is?) that "public" indeed had way too many rights - so it was possible to read and write data from/to any table. This could be fixed. But the BIG QUESTION still remains: how could this happen? We have not found the reason what or who changed the "public" role, so I don't know if that was an admin or a bug or anything ... :-k

ta5

Thanks! Thats strange...
Thomas

space

Once you make changes to a Users Security you will need to Sync that user with the security thru Navision. uyou have to make the change in SQL then open Navision and go to Tools, Security, Windows login Highlite the user and then go Tools, Security, Synchronize Single Login. This will update the security. If the user is logged into navision they will need to close it and open it back up for the security to take place. you can also do this with Database Login as well.