Microsoft KB Archive/66678

= INF: Providing Application Security Through Triggers in SQL =

Article ID: 66678

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition
 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q66678



SUMMARY
This articles discusses how a form of application security can be achieved without hard-coding user names and passwords into an application. This allows people to use many different front-ends to view the information, but allows data modification to be done only from specified applications.



MORE INFORMATION
Using triggers and SQL Server system functions, it is relatively easy to provide this type of application security. The following example illustrates one of the ways you can implement application-level security:

CREATE TRIGGER trigname ON tblname FOR UPDATE, INSERT, DELETE AS   IF (SELECT DISTINCT(program_name)      FROM master..sysprocess      WHERE hostprocess = HOST_ID) != &quot;app_name&quot; BEGIN RAISEERROR 3000 &quot;Table may only be modified by app_name&quot; END

NOTE: &quot;app_name&quot; is the name of the application you want to allow to have permission to modify the table.

This trigger could be modified by placing the app_name(s) into a table and checking to see if the name of the application that wants to modify the table has permission (existing in the application table). This would allow several applications to have modification power. You could even allow certain applications to only update certain fields or tables.

NOTE: Applications must make a call to DBSETLAPP to register their names.

Additional query words: Transact-SQL triggers

Keywords: kbusage KB66678

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.