June 27, 2011 at 1:30 am
Hi
How can i find the Windows login name connected to the system?
Environment: Users are connecting the Terminal Server with their Windows logins (remote logins)
and opens a program with their SQL logins or SA.
i need the windows login name.
Thanks.
cemuney
June 27, 2011 at 1:38 am
No way, AFAIK.
I had to do something similar in the past and I ended up changing the program name property in the connection string to add the connected windows login. Is this a possible strategy in your scenario?
-- Gianluca Sartori
June 27, 2011 at 1:43 am
No way.
i cannot change the programs connection string.
thanks anyway.
June 27, 2011 at 3:34 pm
hi ,
why dont you use a ddl , database for events trigger , it will capture the app login and domain user
Regards
Jody
June 28, 2011 at 12:28 am
Thanks but
thats the reason that i need WindowsNt\LoginName, i am also using DDL trigger.
INSERT INTO dbo.tbDropTableLog(EventTime,EventType,ServerName,DatabaseName,ObjectType,ObjectName,LoginName,UserName,HostName,CommandText)
SELECT
REPLACE(CONVERT(VARCHAR(50), @xmlEventData.query('data(/EVENT_INSTANCE/PostTime)')),
'T', ' '),
CONVERT(VARCHAR(15), @xmlEventData.query('data(/EVENT_INSTANCE/EventType)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ServerName)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/DatabaseName)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectType)')),
CONVERT(VARCHAR(40), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)')),
CONVERT(VARCHAR(100),@xmlEventData.query('data(/EVENT_INSTANCE/LoginName)')),
CONVERT(VARCHAR(30), @xmlEventData.query('data(/EVENT_INSTANCE/UserName)')),
CONVERT(VARCHAR(50), (select hostname from sys.sysprocesses where spid =@@SPID) ),
CONVERT(VARCHAR(MAX), @xmlEventData.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
EVENT_INSTANCE/LoginName
EVENT_INSTANCE/UserName
But WindowsNtloginname doses not exists in this event.
cemuney
June 28, 2011 at 12:40 am
http://qa.sqlservercentral.com/Forums/Topic1126293-391-1.aspx#bm1126863
this has been discussed in the above post as well. Since the users are connecting via terminal services and using sql logins theres not much you can really do.
June 28, 2011 at 3:09 am
User connect the Terminal server first with the windows login and then connect the SQL server with SA how its possible ,it will show SA because users connectiing the SQL server with SA not windows Login
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
June 28, 2011 at 3:32 am
Yes that is the question!
SQL server can catch the lots of values like HOSTNAME even if logins with SA.
(SA is just an example.
Ofcourse they have their own SQL Server logins and passwords. (Sql Authentication)
The aim is to catch the REMOTE Server login name for auditing.
User can connect the Terminal Server and open SSMStudio and delete something with SA password.)
thanks.
June 28, 2011 at 7:01 am
Cem Uney (6/27/2011)
HiHow can i find the Windows login name connected to the system?
Environment: Users are connecting the Terminal Server with their Windows logins (remote logins)
and opens a program with their SQL logins or SA.
i need the windows login name.
Thanks.
cemuney
Have you considered turning the SPROC into a CLR assembly and getting it via C# or VB.NET?
What about using WMI via VBS and passing to the SPROC or vice-versa via temp .TXT file or table?
Just a thought but I'm pretty sure one of those creative approaches might help, if I understand you....
Best,
Rene
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply