March 16, 2017 at 8:55 pm
We have a lot of users that login to a Terminal Services server and we want to be able to determine who is logging into SQL Servers using SQL Native accounts (web service accounts) instead of their Windows account.
Unfortunately there is nothing that differentiates the real person when using the SQL Native accounts from a Terminal Server since the Host, the IP and the LoginName are all the same.
Was wondering if anyone had advise on how to automatically have Windows append the user name to the application property when connecting from SSMS to SQL. Maybe through Local Security Policy or Group Policy? I figure that's the only way to differentiate individuals that have the same footprint otherwise.
Any thoughts?
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
March 22, 2017 at 9:02 am
I don't know if this will help but it helped me locate users logged in from multiple pc's.
selectdistinct
loginame,
hostname
from master..sysprocesses
whereloginame IN (
SELECTloginame
FROM(
selectdistinct
loginame,
hostname
from master..sysprocesses
where loginame not in ('sa')
and (loginame not like TEST.%')
) as T
GROUP BY loginame
HAVING COUNT(Loginame) > 1)
order by loginame, hostname
Regards,
Matt
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply