June 15, 2014 at 12:39 pm
Hi,
Front end applications in my projects use the users' windows ids to get windows authenticated connections to SQL Server 2008 databases.
Since the applications modify data in tables and not necessarily through stored procedures, we have had to give "db_datawriter" role membership to all these windows authenticated users.
But this gives them a free hand to log into the databases using SSMS or even other clients, and potentially run any DML statement.
I'm looking for ways by which write access can be denied if it is not through the front end applications.
At the same time users should be allowed to read data i.e. run select statements through SSMS.
1) Encapsulating all application queries into stored procedures is out of my control and so application needs to run raw insert/update/delete statements.
2) Even if only stored procedures were used for DML, users could still directly run stored procedures via SSMS. So, this is also a problem.
3) Removing windows authentication and using two separate sql authenticated logins - read-only (for users) and read-write (for application) is an option. But we do not want to go this route unless left with no choice.
Have we exhausted all options?
Is there any other clever thing that can be done here please which we haven't been clever enough yet to think of? :smooooth:
June 15, 2014 at 1:28 pm
Had a look at "Application Roles".
I think this is what we are looking for. Will try out and post back if the objective gets achieved or not.
There are some gotchas in the comments of this article that we'll need to be aware of:
June 16, 2014 at 6:10 am
Logon triggers might be a solution, have a look.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply