Managing Application Security Using a Proxy User

Security does not have to be implemented on SQL Server. If the application is developed using three-tier architecture, objects can use roles, users, and other security features of Component Services (in Windows 2003 and 2000 Server) or Microsoft Transaction Server (on Windows NT) to implement security. Security is sometimes also implemented inside the client application.

In both cases, database access is often accomplished through a single database login and user. Such a user is often called a proxy user.

Note 

The worst such solution occurs when the client application developer completely ignores SQL Server security and achieves database access using the sa login. I have seen two variants on this solution.

One occurs when the developer hard-c@des the sa password inside an application. The administrator is then prevented from changing the password (or the application will stop functioning) and the security of the entire SQL Server is exposed.

The other occurs when a developer stores login information in a file or registry so that it can he changed later. Unfortunately, it can also he read by unauthorized people, and again, SQL Server security is compromised.