I have a sql server 2005 database with Delphi 2006 in the front end and for querrying and reporting we use MS Access 2003 by connecting to this database via ODBC connection. I recently found out that the SQL Server 2005 data connected thus can be edited (updated) from MS Access. I do not want end users to modify/update the SQL Server 2005 data from MS Access while I also want them to have the ability to insert/update/delete rights using the appropriate application interface. For now, I am handling this by creating a user id that is not permitted to update, insert and delete and using the same account in the ODBC. Is there a way in SQL Server 2005 you can control insert/update/delete rights for all users that will be applicable only in the ODBC mode?
Any help will be greatly appreciated.
thulo
Hi Thulo,
If I understand your question correctly, you want to be able to grant permissions to database objects based on the type of the client (ODBC, OLEDB, SqlClient, etc.), is this correct? Unfortunately, this isn't possible by design - the main goal is to provide the same functionality no matter what client is used. Instead, the SQL Server security model recommends what you are actually already doing - grant permissions "per database user". The user is the main permissions-related abstract here (having in mind the schemas, too). The user is related to the corresponding login object, which controls the connectivity part (having in mind endpoints permissions, too).
I understand that the user-based permissions concept might seem like more work, but it would pay off long term when the requirements to your application change.
HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Hi Jivko,
You got my question right and that answers my question. Thanks so much for your help.
thulo
