September 27, 2008 at 5:50 am
Hi,
My DotNet application accesses its back-end database using mixed authentication. I want that the user should NOT be able to perform SELECT / UPDATE / INSERT / DELETE etc. directly on the database using SQL statements. Instead of that it should always use the already created Stored Procedures. The SP's have the desired SQL statements to perform application specific operations on the database.
Thank you in advance,
Ajay
September 27, 2008 at 6:04 am
Hello,
You can create a custom DB Role. Grant Execute permission to this Role for all the required SPs. Remove all other Role memberships and unwanted permissions for the users. You can't remove Public membership, so just ensure it has no unwanted permissions.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
September 27, 2008 at 9:04 am
I would recommend that you examine the use of an "Application Role". You can search SQL Server Central there has been a number of articles explaining it use. In particular one by Brian Kelly on the security implications of application roles at:
In addition there is a rather long forum post on the subject at
http://www.sqlservercentral.com/Forums/Topic533262-146-1.aspx
The most salient point is when a user invokes an application, which in turn invokes the application role that application role dictates what the user can or can not do. And you define what permissions the application role has when you first define the role, so it gives you rather complete control. BOL also has a rather length discourse on application roles, starting with how the user logs on and how to / when to invoke the sp_setapprole. Also review the stored procedures sp_setapprole and sp_unsetapprole.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply