3

I have a database user -- let's say "x" -- and I want to know how many Windows (desktop) users are using this database user (who2 or who is only give the current result, I want to know the entire history)?

Actually this question arise when you join any new organization and you don't know how many Windows or desktop users are using a specific database user. Is there any way to know this? How do you track all Windows users who are using this (x) user credential?

2
  • What's your end goal here? Just to document? Prevent too many people from using a single login? Jul 1, 2015 at 18:36
  • well i have a user in our database and many windows users are using this but i exactly don't know how many windows users has access on this user and i also can not disable it or remove it. yes i use the login trigger to track all user going forward.
    – Anil Joshi
    Jul 2, 2015 at 7:35

2 Answers 2

5

You could use a logon trigger perhaps, going forward (see here and here).

For historical information, by default, SQL Server doesn't even track successful logins (though this can be turned on - however it means you will need to parse the SQL Server log for some little bit of information). So while you might get some information (such as which workstations were using which logins) from the default trace, it would rely on them performing actions that get logged there (and would only go back as far as your default trace data goes anyway).

Also see this answer.

2

From a historical perspective, if login auditing was enabled on the server then you could see the logins in the SQL Log and use the IP to trace back to user's workstation or server. This is useless, however, if users are remoting into the SQL host and making a local connection to it (though they really shouldn't be...)

If that's not available to you, then either enable login auditing or setup an Extended Events trace to track user's workstations/IP as they login to the system.

If this is a "development box" (read: non-production) and you're concerned about too many users sharing a credential, a (potentially) drastic option would be to lock the account and see who comes to complain. This would allow you to easily assign new, individual credentials you can control to the users and is more likely to catch someone who logs in infrequently since you can keep the login disabled for an extended period of time but may not want to dig through Extended Event logs for days/weeks/months.

Finally, you could always send out an email blast and see who responds. It probably won't give you the full picture of who has the credentials, but could provide a good start and open a dialog with those who use them most frequently if you plan on making changes in the future.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.