HELP!!! I have been trying for several days now to grant a non-IT employee the permission to add Windows Logins to our SQL Server Option Navision database. Using mbsonline, I have found the objects I need to give him access to in order to see the required Security objects. I have given the user db_accessadmin, db_securityadmin and db_ddladmin,db_datareader, db_datawriter permissions in the Navision database. I have also granted him Security Administrator Server Role access. Yet every time he tries to add a Windows Login he receives a SQL error that says he does not have the necessary permission. Do I have to make him a System Administrator??? By the way - it is not a problem with the user not being in the domain. We have confirmed that the the users we are trying to add to Navision already exist in the domain. In order to comply with Sarbanes-Oxley I have to push the security administration out of IT and into the Business Units.
I have the same situation and its work. My user is an SQL Login and have only the SecurityAdmin, and is db_owner in the Navision DB. Good luck
Interested to hear that you moved security to the users. We have taken the opposite route and retained responsibility for security within IT. What was the primary reason for moving the security roles? By the way have you a result for the SQL admin conumdrum?
Try making the user a member of the securityadmin fixed SERVER role. For versions prior to 4.0, adding a new Windows Login results in the following SQL calls: sp_grantlogin sp_grantdbaccess sp_grantlogin requires that the user is a member of either the sysadmin or securityadmin fixed SERVER roles. sp_grantdbaccess requires that the user is a member of either the db_accessadmin or db_owner fixed DATABASE roles. You can remove membership of db_securityadmin, db_ddladmin, db_datareader and db_datawriter fixed database roles, for this purpose.
Does anyone know if this has changed in version 4.0 or later?
These SP are the standard way to add login to SQL Server. So I think are the same in recent NAV versions.
Right know I can’t confirm you. But tomorrow I can try to execute SQL profiler to see witch SP NAV executes during add login.
Well I tested it myself and unless I give the user membership of the db_owner database role, then I keep getting errors. So it seams that with NAV 4.0 SP3 it does need this role also.
I also got this error today and this post helped me.