Any good database design considers security as part of the overall system design. In addition to controlling who can see what data -- something you can control through users and groups -- it is also important to find out who's actually viewing and, more importantly, modifying data. You'll want to know when a record was created and by whom, when it was last modified and by whom, and so forth, and you'll want to store the information in a set of security fields. For optimization work you might want this information logged into a file for analysis.
The key to establishing these fields is the use of system functions, which are a set of global variables that return information about objects, values and settings. You'll find a list of the system variables you can use in SQL Server Books Online. Among the ones of most interest are: Current_TimeStamp, Current_User, Session_User, and User_Name. Some of these functions take a @@ prefix, and in previous versions of SQL Server they were called global variables. But they aren't variables per se and don't behave as such.
This information can be stored in a set of fields in the tables that they relate to, fields that aren't available for viewing by users without the appropriate permissions. More often they are stored in a set of fields in a security table that can be better protected. Information is written to these fields as part of any transaction that creates or modifies a record. Often a user defined function is called
Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.
This was first published in March 2005