Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Denying write access if not connected via the application. Expand / Collapse
Author
Message
Posted Sunday, June 15, 2014 12:39 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 9:57 AM
Points: 50, Visits: 353
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?
Post #1580948
Posted Sunday, June 15, 2014 1:28 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 9:57 AM
Points: 50, Visits: 353
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:
Post #1580955
Posted Monday, June 16, 2014 6:10 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 5:39 AM
Points: 428, Visits: 926
Logon triggers might be a solution, have a look.

http://msdn.microsoft.com/en-us/library/bb326598.aspx



Post #1581082
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse