[dba-SQLServer] Determining Logged in Users?

Dan,

I use this, which can be called from anywhere from the Access FE - usually on the startup/Exit form is sensible.

AddAuditEvent ("Log Off - v" & strAppVersion)

and / or

AddAuditEvent ("Log On - v" & strAppVersion)


'-------------- Code Start --------------------
Option Compare Database
Option Explicit

Public Sub AddAuditEvent(strMsg As String)
'***********************************************'
'                                               '
'   This Procedure handles all audit events for '
'   this application. It logs to the audit      '
'   any string passed to it from anywhere       '
'   within the application.                     '
'                                               '
'   This function automatically adds the        '
'   username, machine name and timestamp        '
'   to the audit record                         '
'                                               '
'   Created by Beny Aycardo                     '
'   Started on 23 March 2006                    '
'                                               '
'***********************************************'
    Dim sql As String
    Dim cnn As ADODB.Connection

    
    On Error GoTo ErrHandler
    Set cnn = New ADODB.Connection

        'Clean up the audit message string so it can be passed to the
        'stored Procedure
    strMsg = Replace(strMsg, "'", "")
    
        'Add a new record to the error log table
        'craft the call to the stored procedure
    sql = "stprAddAuditLog " & _
                "'" & GetUserNameAPI() & "'" & ", " & _
                "'" & GetComputerNameAPI() & "'" & ", " & _
                "'" & strMsg & "'" & ", " & _
                "'" & strAuditTable & "'"
    
    'Debug.Print sql
    
    cnn.Open DbADOConStr
    cnn.Execute (sql)
    
    
ExitHere:
    On Error Resume Next
    cnn.Close

    Set cnn = Nothing
    Exit Sub
    
ErrHandler:
    strErrMsg = "ModAudit.AddAuditEvent "
    strErrMsg = strErrMsg & Err.Number & " - " & Err.Description & vbCrLf & vbCrLf
    
    ErrHandle (strErrMsg)
    Resume ExitHere
End Sub
'---------------Code End--------------------------


'------------ SQL SERVER SPROC---------------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[stprAddAuditLog]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[stprAddAuditLog]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/*		
		This Stored Procedure adds a new record
		to the Audit Log table. 
	*/

CREATE PROCEDURE [dbo].[stprAddAuditLog]

	@UserName NVARCHAR(12),		-- User Name
	@MachineName NVARCHAR(12),	-- Machine Name
	@AuditMsg NVARCHAR(1500),	-- Audit Message
	@AuditTable NVARCHAR(100)	-- Destination Audit Table Name	
AS
	SET NOCOUNT ON

	DECLARE @SQL NVARCHAR(4000)
	
	SELECT @SQL = 'INSERT INTO ' + @AuditTable
	SELECT @SQL = @SQL + ' (UserName,MachineName,AuditMsg) VALUES (' + ''''
	SELECT @SQL = @SQL + @UserName + '''' + ',' + ''''
	SELECT @SQL = @SQL + @MachineName + '''' + ',' + ''''
	SELECT @SQL = @SQL + @AuditMsg + '''' + ')'
		
	EXECUTE (@SQL)

SET QUOTED_IDENTIFIER OFF 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

'-------- END SPROC ---------------------



-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Saturday, 11 July 2009 12:47 AM
To: SQL Server List
Subject: [dba-SQLServer] Determining Logged in Users?


If I have multiple Access FE's / one SQL BE, and I'm not using Access's
username/password method, what can I do to see who is logged in to database?


I'll be using ODBC table links to the BE.  Does SQL keep track of users who
are connected?  How could I reach that list?

Thanks!
Dan

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


This email and any attachments may contain privileged and confidential information
and are intended for the named addressee only. If you have received this e-mail in
error, please notify the sender and delete this e-mail immediately. Any
confidentiality, privilege or copyright is not waived or lost because this e-mail
has been sent to you in error. It is your responsibility to check this e-mail and
any attachments for viruses.  No warranty is made that this material is free from
computer virus or any other defect or error.  Any loss/damage incurred by using this
material is not the sender's responsibility.  The sender's entire liability will be
limited to resupplying the material.