Finding User ID in database

B

Bob

I am creating an audit trigger in SQL Server. I would like to pull the
Windows User ID used to connect to ProjectPro or PWA. All I have been able
to find so far (through SUSER_SNAME and USER) is the account MSPS uses to
connect (MSProjectUser).

Any suggestions?
 
E

Earl Lewis

Look in the resource tables. When resources are added a user account will automatically be created. If you supply a windows user name with the resource then the account will be setup to use windows authentication. If not, it will use project server authentication.

Earl
I am creating an audit trigger in SQL Server. I would like to pull the
Windows User ID used to connect to ProjectPro or PWA. All I have been able
to find so far (through SUSER_SNAME and USER) is the account MSPS uses to
connect (MSProjectUser).

Any suggestions?
 
B

Bob

Thanks Earl, but that is not quite what I was looking for. I want to know in
a stored procedure or trigger, who the active user is that initiated the
process. If I understand correctly, and maybe I don't, once you log on to
ProjectPro or PWA the application connects to SQL Server as "MSProjectUser"
or "MSProjectServerUser". So If I look at the DB function SUSER_NAME() I get
MSProjectUser, but I want to get "Fred".
 
E

Earl Lewis

You're looking for session information then. Not sure where that's stored in the db but I would imagine its somewhere in the msp_web-* tables.

Sound like you have direct access to the SQL server. Why don't you setup tracing and watch a login and some other actions to see which tables are being accessed?

Good luck.

Earl
Thanks Earl, but that is not quite what I was looking for. I want to know in
a stored procedure or trigger, who the active user is that initiated the
process. If I understand correctly, and maybe I don't, once you log on to
ProjectPro or PWA the application connects to SQL Server as "MSProjectUser"
or "MSProjectServerUser". So If I look at the DB function SUSER_NAME() I get
MSProjectUser, but I want to get "Fred".
 
E

Earl Lewis

Bob,

This is the stored procedure that I found in a trace that will give you a users name:

MSP_WEB_SP_QRY_GetAllResourceForResId

This procedure needs a parameter of a userid number - mine is 176. So when I login and the trace gets to this call it looks like this:

exec MSP_WEB_SP_QRY_GetAllResourceForResId 176

Of course, this stuff is called from a users web session and the userid is probably stored in a cookie so that future calls can make use of it as well. So the bottom line is, I'm not sure any session data is stored in the database that you could use in any meaningful way.

Hope that helps.

Earl
Thanks Earl, but that is not quite what I was looking for. I want to know in
a stored procedure or trigger, who the active user is that initiated the
process. If I understand correctly, and maybe I don't, once you log on to
ProjectPro or PWA the application connects to SQL Server as "MSProjectUser"
or "MSProjectServerUser". So If I look at the DB function SUSER_NAME() I get
MSProjectUser, but I want to get "Fred".
 
Top