Getting curent logged application user

Question

Getting curent logged application user

radu-929763

SSC Rookie

Points: 37
More actions
November 4, 2008 at 2:33 am

#195441

Hi !
I must create an audit table. I want to create my audit using after triggers but i don't know how to get the curent logged aplication user. Those users are stored into a table from database , so i am not intersitng to get the name of the login, i have only a login name for all my users. I want to get username for example like "John" , not "dbo", name which is usually stored into a stored procedure.
Thanks !

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SQLZ SSChampion Points: 12872 More actions · Answer 1

If you're already using stored procedures why don't you skip the use of triggers and just add your auditing code into the body of the stored procedure? In that way you have access to the username that was passed into the stored procedure.

Karl
source control for SQL Server

steveb. SSC-Forever Points: 46733 More actions · Answer 2

try this code to get the current user

SELECT SUSER_SNAME()

SQLZ SSChampion Points: 12872 More actions · Answer 3

I don't think the OP want's the SQL Server user. He want's the application user, which is passed through to the stored procedure as a parameter.

I might be wrong though - won't be the first or last time.:doze:

Karl
source control for SQL Server

radu-929763 SSC Rookie Points: 37 More actions · Answer 4

Thanks for replys! Yes , sqlz you are right i try to get the current user for my application. I am studying to do this using ContextInfo. For now i am thinking to do somethink like that:

1. When i open a conection to the server (here i don't know quite well what to do, can sql server notice when a request for a conection comes from an let's say c# language ,and how i will pass the name "Jhon" throught the server :ermm: ,should i use clr ? i know that this mechanism works in mssql 2000 so there must be another way ) , then execute this code on the server:

DECLARE @user varchar(128)

SET @user='John' -- which is the current application logged user

DECLARE @context binary(128)

SET @context=CONVERT(binary(128),@user)

SET CONTEXT_INFO @context

2. Then in my trigger i do somethink like that:

DECLARE @user varchar(128)

SET @user=REPLACE(CONVERT(varchar(128),CONTEXT_INFO()),CHAR(0),'')

--SELECT @user

Am i on the right way ? using my above ideas ... Please give me an feedback !

Using stored procedure i can get that user through an output parameter, i guess...

Thanks !

SQLZ SSChampion Points: 12872 More actions · Answer 5

To be honest I don't much about context_info but I think it's much simpler if you do away with the idea of trigger and just do all of your coding within the stored procedure, which you're already passing a user name to.

Karl
source control for SQL Server

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

If you must do it in triggers (and seriously, you would be much better off using Karl's suggestion of stored procedures), then at least you should not use CONTEXT_INFO which is probably the worst way imaginable to store information global to your session. A temporary table would be much better.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

radu-929763 SSC Rookie Points: 37 More actions · Answer 7

Thanks for replys !

I will study more ,how to do it. Right now i solved my above problem , the only facts that i am thinking now is if i will use this design or not.

Thanks

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

I hope that this means that you are not going to use CONTEXT_INFO, it is a seriously bad idea.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]