March 4, 2009 at 7:45 am
Hi guys,
I have a project, normally users are authenticated by active directory, however, when there is an error, I want to record this error. Due to sometimes I wouldn't know what type of error it could be, so I use a special account (name: LogError) to log all error messages, and I assign that special account special permission in the database, my question is, in SQL, how do I know the user's name even when he is using this
I have a connection string which is using a specific account/pwd to let SQL know it is user XXX that encounters an error even when the LogError connectionstring is used to log the error? Currently I got NULL for @User in the spLogError
Thanks in advance.
here is how I do it:
Connection String:
PergamumLogErrorConnectionString:
connectionString="Data Source=10.40.1.57;Initial Catalog=Pergamum;User ID=LogError; Password=Password1;Connect Timeout=200;" providerName="System.Data.SqlClient"
Code:
EventLogDB.LogError("Failed to unlock the locked file", ex)
Public Shared Sub LogError(ByVal Msg As String, ByVal e As Exception)
'Stored Proc method:
Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("PergamumConnectionString").ConnectionString)
Dim comm As New SqlCommand("spLogError", conn)
comm.CommandType = CommandType.StoredProcedure
Dim Param As SqlParameter = comm.Parameters.Add("@Msg", SqlDbType.VarChar)
Param.Value = Msg
Param = comm.Parameters.Add("@Error", SqlDbType.VarChar)
Param.Value = e.ToString()
Try
comm.Connection.Open()
comm.ExecuteScalar()
Catch ex As Exception
Throw New Exception(ex.Message)
Finally
comm.Connection.Close()
comm.Connection.Dispose()
End Try
End Sub
ALTER PROCEDURE [dbo].[spLogError](@Msg Varchar(255), @Error Varchar(1000))
AS
SET NOCOUNT ON-- Required for RAISERROR to work properly via AODB
DECLARE @Err INT, @RCnt INT
Declare @User VARCHAR(50)
Select @User = [Name] From [User] Where Login = suser_sname()
INSERT INTO dbo.[ErrorLog] (Msg, Error, [User], CreatedOn)
VALUES(@Msg, @Error, @User, Default)
March 4, 2009 at 8:02 am
In this case you would need to get the user name from the application and pass it to spLogError.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 4, 2009 at 3:52 pm
Hi
Since you use your ErrorLog user account for the ConnectionString you have to specify any other user information by your self. If any other user account is connected you can use one of the following:
SELECT SYSTEM_USER
SELECT USER_NAME()
Greets
Flo
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply