Performing certain tasks triggered by login | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Performing certain tasks triggered by login

Due to auditing requirements, we have a need to perform certain tasks upon login by certain ids. In psuedo code, we want to do the following: Upon login of any id
if the id is abc, xyz or rst
then
initiate tracing for this session
change the password for this id
other tasks …
end if. The ideal situation is a login trigger that is initiated by all logins which will perform the id check and perform the tasks required if the ids in question are used for login. Such trigger exists in ORACLE [xx(]. I have not been able to find any such trigger documented. There are auditing features that will capture the login to a trace file and the trace file can be loaded into a table afterward but I wish to perform this on a real time basis. Any assistance would be greatly appreciated. Thank You.
Articles about auditing in SQL Server
http://www.microsoft.com/technet/security/prodtech/dbsql/sql2kaud.mspx
http://www.databasejournal.com/features/mssql/article.php/2243271
andhttp://www.devx.com/getHelpOn/10MinuteSolution/20544 – about trouble with triggers. If budget permits can buy third party tool defined herehttp://www.sql-server-performance.com/lockwoodtech_log_navigator_spotlight.asp to accomplish the task.
HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Satya, Thanks for the reply and information. However, the key requirement for me is to perform a list of tasks during the login of a particular id on a REAL TIME BASIS. Whenever an id in question logs into the instance, some tasks (change the password or some other tasks) will be performed immediately. That’s why a login trigger would be the perfect solution if such a feature exists in SQL Server. This trigger, if it’s possible, will be fired upon login by any id and can check to see if the lists of tasks should be processed for this id. Real time processing is what I need. Auditing will not satisfy this requirement. Even if I process the audit log every 30 seconds to find all relevant logins and process the tasks against this list of ids, it would not satisfy the requirement of real time processing. We need a list of tasks to be performed at the time of the login. Thanks, Jeff
Can this be done at an application level? Ie, you call a simple stored procedure passing the current login, user number whatever, and it can go off and process the required tasks? Or must this be done no matter where the login originates from, ie Access, Excel, Query Analyser etc Remember that some applications will log in more than once during a typical usage session. I know of no global way to respond to login events. You cannot define triggers on system tables in MSSQL (ie, sysprocesses), and there are no stored procedures which are guaranteed to be called during login which you could piggyback your code into.
Chappy, We want to perform the list of tasks for all logins from any and all sources (current and in the future). It would be an unrealistic requirement to have all current and future applications (custom or vended) to include a specific section of processing to verify and perform some tasks. My research did not uncover any objects similar to a login trigger for SQL Server (which would be perfect for my requirements). I just wanted to ask the general community to see if they know of such an objects in case I have overlooked some documented or undocumented features. Thanks for confirming that no such object exists. We will have to explore other options.
Jethro
]]>