Post reply

How to allow Acces to SQL Server connection, but no login to SQL Server

  • October 10, 2009 at 10:02 am

    #210762

    Hello!

    I run a SQL Server 2008 db. I gave a user data_reader access to a certain db. He uses this to link in Microsoft Access to certain tables in the database.

    I would like to avoid now that the user can access the sql server via Toad, SQL Server Management Studio or another program. Can this be done, without restricting him in his use of Microsoft Access?

    Hope my question is clear, thank you very much for your help!

    Best Regards

    Koen

  • October 13, 2009 at 11:46 pm

    #1065497

    Hello,

    Sorry to post this again, but it's rather urgent.

    Thank you for your thoughts!

    Koen

  • October 14, 2009 at 12:06 am

    #1065502

    Seeing as you are hard up for an answer, I'll offer this; perhaps you could remove the other undesirable apps from machines in your organisation and not grant users installation rights.

    This might be completely off topic, but I have a user that requires to use tables in our SQL environment via MS Access because thats what he has experience in for reporting etc. I achieved a desirable result by using DTS to export the data sets he wanted to a file share to import into MS Access.

    Food for thought perhaps 🙂 Waste of time possibly 😛

    - DamienB

  • October 14, 2009 at 12:30 am

    #1065506

    If he has access, he has access..

    I don't know what kind of authority you have but if it is sufficient you say that those tools are not to be used. Period.

    If they cause problems you can hold that in reserve when someone asks why.. Then you can say, that using that application causes locking and blocking in the server and affects everyone.. Be sure what you say is true..

    I like the other posters idea, remove the tools and disallow re-adding them..

    CEWII

  • October 14, 2009 at 2:27 am

    #1065529

    Also make sure that the user can access only the db he needs, as a "db_datareader".

    However, it would be better if the user had only the right to run certain stored procs in that database. I'm not sure if you can achieve this using Access (hadn't work with Access for over 10 years...:)). Maybe you'll consider replacing the Access app with a .NET one?

  • October 14, 2009 at 4:38 am

    #1065574

    Hello all,

    Thanks for your ideas!

    However, I'll be unable to uninstall all programs as everyone has its own laptop with admin rights.

    But no problem, we'll live with it 🙂

    Thanks!

    Best regards

    Koen

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply