How can I protect user use other program to connect to MSSQL

suthipong

Dear Sir
I have some problems about user connect to MS SQL with ODBC with others Software and to modify data in Navision ..
I need your help that how I can protect it ...

Thanks

kine

TO be able to connect to SQL directly, someone must to add special permissions to this user. If you only add user into NAV, it will not grant permission to connect to the database and work with the data from outside NAV. It means that someone set the users as db_owner etc. Users need only Public permissions, which grants no rights to the data, only to logging into the DB... Check the SQL permission and remove any permissions set directly for the user accounts...

kriki

[Topic moved from 'NAV/Navision' forum to 'SQL General' forum]

suthipong

I use with your solution but I still connect with ODBC ... with other Program ... How Can I protect ODBC to connect my Database SQL Server with other Software etc Access .
Regards.
Suthipong

kine

If you are able to connect, it means that there are some permissions on SQL allowing you to read the data. Check server roles on the account. There must be no server role.

suthipong

Hi Kine
I still problem about that user role I remove it all. and if I remove permit public only I still connect MS SQL by ODBC but in navision I can't use it the same user don't permit db_owner ...

about this problem is : one user he can use access to connect MS SQL with his login to delete data in Navision .. so I try to protect to use other App to connect to navision by ODBC ..

Regards.
Suthipong

kine

May be I misunderstand what you wrote, but I will write it again...

The user must have only PUBLIC permissions to the database to be able to work with the NAV. Nothing else. No db_owner, no other server roles etc. This will prevent the user to do anything from outside, and will be able to log into NAV without problem.

garak

one simple (fast) solution is:
Create a job on SQL Server agent over sys.dm_exec_sessions. If a application / defined user will connect (SPID > 50) to your defined database, and it's not NAV or Management Studio or SQLAgent or LogReader or some other needed, you can kill the session and log the application / user off.

Regards

suthipong

Hi Garak
your suggestion is to check sys.dm.exec_sessions but I can't find it in SQL 2000 .. and in SQL 2000 I found SPID more than 50 and SPID that I found is Navision connect to DBMS but I can't find my test that I have another application (Access 2007) to connect DBMS too and I can't find SPID of this ....
Can you explain me more ...?
Regards.
Suthipong

p.willemse6

Maybe, maybe it would be wise to follow a basic SQL education. These questions really go back to basics of SQL...