SQL user problem

vikram7_dabas · 2008-04-01

I have created new sql databse(.mdf) in navision by restoring the .fdb database,that contains the userID "SUPER" and PASSWORD IS blank,the .mdf database has sucssessfully restored,then I have closed that database(.mdf) when i again OPENING THAT DATABASE BY USING USEDID SSUPER AND PASSWORD BLANK,THEN IT GIVES ME THE ERROR Combination of USERID and Password is incoorect,I m not able to open that database(.mdf) and I have also created that userID "SUPER" and Password Blank in SQL server 2000,then again it is showing me d same error.give me the solution.Then again I have created d new .mdf databse by deleting the previous one and restored d the databse successfully then I have created the USERID "XYZ" abd password Blank and the same userIdD I have created in SQL SERVER 2000,and when I have closed that .mdf Database then again I m not able to open that (.mdf) databse same error occurs.and plzz tell me that while creating USERID where we have to put first in .MDF database or in SQL databse.

ssingla · 2008-04-01

Check the userid and password after restoring in the Tools : Security : Database Login.
Does the user id u are using has been defined in the backup database. I guess that can be the problem.

same userIdD I have created in SQL SERVER 2000,

You are using 2000 :-k :-k

kine · 2008-04-01

When restoring NAV backup on SQL, user passwords are not restored, because they are set on SQL and not in NAV. It means you need to create the user "SUPER" on MS SQL server first and set the password there. Good to set this user as Server_Admin to have all rights. After restore, you will be able to login as this user without problems...

kriki · 2008-04-07

[Topic moved from Navision forum to SQL General forum]

RudiRutsche · 2008-04-09

kine wrote:

It means you need to create the user "SUPER" on MS SQL server first and set the password there. Good to set this user as Server_Admin to have all rights.

additional:
You don´t have to set the user as "Server Admin" to grand all "navision" rights to the user. If you make "super" a "Server Admin" this will give the user the right on the sql server itself, which might not be exactly what you want to do.

Persson · 2008-05-06

RudiRutsche wrote:

kine wrote:

It means you need to create the user "SUPER" on MS SQL server first and set the password there. Good to set this user as Server_Admin to have all rights.

additional:
You don´t have to set the user as "Server Admin" to grand all "navision" rights to the user. If you make "super" a "Server Admin" this will give the user the right on the sql server itself, which might not be exactly what you want to do.

I am not sure that "Server Admin" is a standard SQL 2005 Database Role. In order to setup a NAV user as a user, who can create and delete NAV Users in NAV without accessing SQL Server 2005, what SQL 2005 Database Role should he/she be assigned? Db_accessadmin or perhaps Db_securityadmin? I am looking for a Server Role with the absolute minimum of rights to do the job.

bbrown · 2008-05-06

The SQL Server Login needs to be a member of the SQL Server Role "Public" and the SQL Database user needs to be a member of the SQL database role "Public". This are both the defaults for these accounts. There is no reason to grant additional SQL access to normal NAV users. Only those users performing development or admin task require additional access.

Persson · 2008-05-07

bbrown wrote:

The SQL Server Login needs to be a member of the SQL Server Role "Public" and the SQL Database user needs to be a member of the SQL database role "Public". This are both the defaults for these accounts. There is no reason to grant additional SQL access to normal NAV users. Only those users performing development or admin task require additional access.

Thanks for reply. But you don't answer my question :-)

I am looking for
- a SQL 2005 Server Role with the absolute minimum of rights
that I can assign to a
- NAV-user
that have to administrate (Create, Read, Edit, Delete) NAV-users in Navision.

"Public" is not privileged enough - you will get SQL-errors in NAV if you try to create a new NAV-user through NAV with another NAV-user just with Role "public". I have tried with "Db_accessadmin" and "Db_securityadmin" but it still seems to fail. I think that "Db_owner" is too privileged a Role to assign a normal NAV-user from HR. Perhaps one should create a new SQL 2005 Server Role (fx "NAVUserAdministrator") just with permissions to CRED in the user-tabel? Then NAV-user from HR could be assigned the Roles "public" and "NAVUserAdministrator"?

bbrown · 2008-05-07

The ONLY SQL roles that are required are Public. NAV handles permissions thru SQL Application Roles.

User doing administration in NAV will need additional roles depending on the type of functions they are doing. The simplest (and common) approach is to make the Admin users members of the SysAdmin server role. In some cases this may be considered too open. In those cases, see the SQL BOL and assign appropriate roles. You should be able to acheive what you are looking for with standard roles.

Persson · 2008-05-07

bbrown wrote:

The ONLY SQL roles that are required are Public. NAV handles permissions thru SQL Application Roles.

User doing administration in NAV will need additional roles depending on the type of functions they are doing. The simplest (and common) approach is to make the Admin users members of the SysAdmin server role. In some cases this may be considered too open. In those cases, see the SQL BOL and assign appropriate roles. You should be able to acheive what you are looking for with standard roles.

#-o Just realized that I only have focused on Database Roles when you talk about Server Roles. I will experiment with Server Roles to night :-) Thanks for the hint.

RudiRutsche · 2008-05-20

I have a bunch of databases to admin on a NAV testserver.

What I detected is, that in most databases i have to give this db roles to the user:
db_datawriter,db_datareader, db_ddladmin and db_securityadmin

But somehow some db require the user to be db_owner as well.

Does anybody know on what this depends on?

stryk · 2008-05-20

What NAV Version do you run? There was indeed a bug in early 4.0 versions where it was necwssary to have all users set to "db_owner", but meanwhile that's fixed ...

With NAV you actually need this:

At lease one "sysadmin" (of course!)
NAV Developers: Server Role "public", Database Roles "public" & "db_owner"
NAV Users: Server- and Databaserole "public"

According to that you should use the "Standard" Security Model instead of the "Advanced" one. Every additional authentication is done by the "Application Roles" $ndo$shadow or $ndo$ar$... .

Why do you have to assign that specific roles? Actually that should be just required for non-NAV logins accessing the NAV database ...

RudiRutsche · 2008-05-20

stryk wrote:

With NAV you actually need this:

NAV Developers: Server Role "public", Database Roles "public" & "db_owner"

Sorry, forgot to mention:
I was talking about developers at all

stryk wrote:

Why do you have to assign that specific roles? Actually that should be just required for non-NAV logins accessing the NAV database ...

Could you explain that any furher? Why just required for non-NAV users?

The current situation is, that the user SUPER has the sever role sysadmin. (just a testsystem)
According to a new security situation i have to change that and after taking this role from the user, i had to give him this db roles.
Would there have been another way?

stryk · 2008-05-20

[quote="RudiRutsche"
Could you explain that any furher? Why just required for non-NAV users?

The current situation is, that the user SUPER has the sever role sysadmin. (just a testsystem)
According to a new security situation i have to change that and after taking this role from the user, i had to give him this db roles.
Would there have been another way?[/quote]
OK, I see ... If you don't want to set the restrictions for that user in NAV, you couold do that by fiddling with the SQL roles, of course, I guess thats easier than doing it in NAV ...

The developers need the "db_owner" role to create/update/delete objects in the Database, e.g. when a NAV table is created, then also the SQL table needs to be created which is not possible with "public" only ...

RudiRutsche · 2008-05-20

stryk wrote:

The developers need the "db_owner" role to create/update/delete objects in the Database, e.g. when a NAV table is created, then also the SQL table needs to be created which is not possible with "public" only ...

For creating, updating, deleting there is the role db_ddladmin!

In most databases a developer can work with the roles db_datawriter (for writing data), db_datareader (for reading data), db_ddladmin (For creating, updating, deleting tables) and db_securityadmin (needed for beeing able to change objects in the ms range (id < 50000).

But in SOME databases, I cannot connect without beeing member of db_owner.
So what I want to know ist, on what THIS depends on.

stryk · 2008-05-20

Hmm ... actually it should work with either of these roles, here from the "Installation & System Management (SQL) PDF:

To create or alter a database, the administrator must be a member of either the
sysadmin or the dbcreator SQL Server server roles. To create a database, the
administrator must also have public access to the model database. To alter a database,
the administrator can alternatively be a member of the db_ddladmin or db_owner
database roles for this database. For more information about security and SQL Server
roles, see Chapter 4, Security and User Setup.

and

As mentioned earlier, Dynamics NAV allows you to specify the level of security that you
want to implement in each database. You can choose between two different security
models:
• Standard Security
• Enhanced Security
The main difference between these two security models is the way in which they
synchronize the Dynamics NAV security system with SQL Server and the way that they
integrate the Dynamics NAV security system with Windows authentication.
To change the security model used in the database, you must be:
• A member of the sysadmin server role on SQL Server or be a member of the
db_owner database role for the database in question.
• Assigned the SUPER role in Dynamics NAV.

and mostly

SQL Server Database Roles and Server Roles
The ability to perform certain activities within SQL Server requires that the users have
the appropriate server or database permissions or that the members of certain server
or database roles. Membership of these roles is not assigned automatically during the
synchronization process. They must be assigned by a SQL Server administrator.
The minimum requirements for carrying out these activities are listed in the following
table:
Dynamics NAV Activity Requires Membership of Server or Database Roles
Invoking the synchronization
process or modifying the
User table
sysadmin server role. Alternatively both a member of the
securityadmin server role and a member of the db_owner database
role for this database.
Creating a database sysadmin or dbcreator server role. Alternatively, the user must have
been granted the create database permission. The user must also
have public access to the model database.
Altering a database sysadmin or dbcreator server role. Alternatively a member of the
db_owner or db_ddladmin database role for this database.
Creating tables within a
database
sysadmin server role or be a member of the db_owner database role
for this database.

In order for a user to create or modify table definitions in Dynamics NAV, they must be
a member of the db_owner database role (the database creator is automatically a
member of this role). Membership of this database role must be assigned outside
Dynamics NAV, after the user has been created in one of the Dynamics NAV login
tables. These are the Windows Login table (accessed through the Windows Logins
window) and the User table (accessed through the Database Logins window). Adding
a user to a database role is done with a tool such as Microsoft Enterprise Manager.
Note that this user will then have all permissions within this database.
Any permissions required on individual tables or views in the database, for users who
are not db_owner users, must be manually granted to the users if the use of external
tools such as report writers is required outside Dynamics NAV.

and so on ... please check it out ...

Howdy, Stranger!

Quick Links

Categories

SQL user problem

Comments