Audrey Ng ( XXXX@XXXXX.COM ) writes:
No special type. The point is that you do not give users right to access
the tables directly. Instead you write stored procedures for the task
and grant users right to execute the procedures. The important point is
that the procedures must be owned by the same user that also owns the table.
Commonly all objects in a database are owner by dbo.
Note that if the users are able to find the stored procedures, they can
still run them from Mgmt Studio.
Keep in mind that application roles can never be safe in a two-tier
application. The application must somehow find the password for the
application role and send it to SQL Server. And if the application can
find it, the user can find it.
I think the safest way is to use a three-tier application with a proxy
login. That is, the middle tier connects to SQL Server with a proxy login,
and the users do not have any direct access to SQL Server. If it's
necessary to know who's who, this can be handled with EXECUTE AS or
I have an article on my web sites where I discuss these topics in more
detail, you may be interested: http://www.yqcomputer.com/
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM
Books Online for SQL Server 2005 at
Books Online for SQL Server 2000 at