Sql permission Error

amarnath

Hi Friends
i was trying to connect sql and navision has a client server configuration mode. We have setup the server using sql server 2005 express edition, navision 5. The problem we face is when we try to do a windows authentication we get a property or select permission. Can you please let me know what is the solution for the error.



I am attaching the error as a screenshot.



Thanks and regards

bbrown

Have you enabled trace flag 4616?

ara3n

Did you register xp_ndo dll file on sql server?

amarnath

Hi friends

both trace flag and xp_ndo has been done . but even then we are getting this error

kine

1) First error is because you need to open the database once as user which is DB_Owner or System admin (it seems like NAV needs to create the system table with license, because you selected "License per database" or there is no license yet on the server).

2) Second error can be side effect of first error - that there is no correct license on the server yet...

amarnath

Hi it works if the user is set with dbowner permission. Can you tell me if i can have many user as a dbowner. will it not create a problem in database ](*,)

bbrown

amarnath wrote:

Hi it works if the user is set with dbowner permission. Can you tell me if i can have many user as a dbowner. will it not create a problem in database ](*,)

Granting DBOwner effectively overrides all other permission settings in the database. The users would have full access to all tables regardless of NAV roles settings. They would also be able to modify table definitions.

A bad idea.

ara3n

Only one user needs to do be dbowner. Once the table is created, you can remove the permission. Afterward all the users should be able to login fine.

amarnath

Hi friends
A query the concept for us is we are 15 user and all have to work in the database. so i have to make all 15 user as dbowner permission otherwise it doesnot work.


but if all the 15 user has dbowner permission it might create problem . Can you please let me know how to work on it. I have a mixed mode authenctication procedure. ](*,)


Other wise tell me the privillege and steps i need to follow in.

kine

If I read the first screenshot correctly, it is about error in permission for creating the table in 'master' database. You need to run the client under account, which is system admin, to be able to create the table once, and to import the license into the table. After that, I hope, will be all ok. Because now it looks like the system was never able to create the needed table in master database and to import the license there...

amarnath

Hi kine

Can you give me a detail sloution on what you said and how to do it. I am little bit confuse and i am very new to navision with sql.

SQLGuru

A small checklist:

In order to create a table in master, you need the sysadmin role.
With Windows authentication, by default you need to be in the Windows Administrators Group of the box where SQLServer resides. Alternatively you can use a Windows account or an account that belongs to Windows Group that has a login in SQL Server with sysadmin role.

SQL Express Ed. needs to configured to accept remote logins, if NAV client and SQLServer are on different boxes.

Let us know how you're doing!

amarnath

Hi SQLGuru,
The connection i have setup is
1) windows xp with sql server 2005 express edition
2) Navision in the same(nav 5) This system is remotely accessible as i have set the surface area configuration properly the other system connecting this system isa xp o/s system which has nav5.0 in it.


The problem i have is i add a windows user id in navision frist and get it in sql management studio where is it set to be in master but i replace it to the navision database. but this user when connecting shows error. what i did later is i created a windows user in management studio for the navision database and add it to navision , but even after doing this it is showing the same error.

SQLGuru

From that I gather that both, nav client and sql server, run on the same box.
we are talking about windows auth, right?

so you need to map a user account that already exists to the windows admininistrators group. this you must do in windows.
then make sure that the windows local group of administrators has a login to sql server and verify that this login has the sysadmin role.

amarnath

Hi Guys
Sorry for a delayed reply. Still now this is not working . i am facing the sql permission error now also. Can you tell if i can change the SQL edition and work on it.