Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Get current user

Status
Not open for further replies.

darude

Programmer
Jun 23, 2003
138
US
Good morning all,
Is there a way to get the current user in a stored proc without passing to a variable in the proc. So, in the where clause - WHERE Staff.Username = currentuser() - which is an access thing. Didn't know if I could do something similar.
 
Check all functions begins with SUSER_ or USER_ in BOL to see if any of these can help you.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
This script returns login information from the default trace created in SQL Server 2005. When the sys.server_principals data is null that would mean the login is allowed via a Windows Group.

sys.traces provides the information for the default trace such as the file path and the max files.

fn_trace_gettable returns the data from trace file(s) in table format.

sys.server_principals is the way you should access server logins in SQL Server 2005, replacing syslogins.

An important thing to note is that the default trace will create up to 100MB (5 20MB files) of event data and then begin wrapping. Also it creates a new file when ever the SQL Server is restarted so you may not have the full 100MB of data if you reboot or restart SQL Server often.

SELECT
I.NTUserName,
I.loginname,
I.SessionLoginName,
I.databasename,
Min(I.StartTime) as first_used,
Max(I.StartTime) as last_used,
S.principal_id,
S.sid,
S.type_desc,
S.name
FROM
sys.traces T CROSS Apply
::fn_trace_gettable(CASE
WHEN CHARINDEX( '_',T.[path]) <> 0 THEN
SUBSTRING(T.PATH, 1, CHARINDEX( '_',T.[path])-1) + '.trc'
ELSE T.[path]
End, T.max_files) I LEFT JOIN
sys.server_principals S ON
CONVERT(VARBINARY(MAX), I.loginsid) = S.sid
WHERE
T.id = 1 And
I.LoginSid is not null
Group By
I.NTUserName,
I.loginname,
I.SessionLoginName,
I.databasename,
S.principal_id,
S.sid,
S.type_desc,
S.name


 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top