Which logins are being used

Question

Which logins are being used

Robert Phillips-476898

SSC Veteran

Points: 231
More actions
September 3, 2008 at 11:05 am

#212057

I would like to audit which logins are being used and then be able to identify and delete the logins that are no longer needed.
I've looked at sysprocesses, sp_who, sp_who2, profiling to find active logins but my problem is that many of the sessions are connecting with Windows authentication which shows up as DOMAIN\Username.
The logins in SQL are a mixture of SQL logins, and Windows logins either DOMAIN\Group or DOMAIN\User.
I can't find a way to link the logins I'm seeing in the above methods to which SQL login is being used for their connection.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

You can use the default trace that is running on SQL Server 2005, it Audits logins and logouts so you can use the Audit Login event to see the logins that are actually being used. You will still see the Domain\UserName, but if that is what you are seeing that is how they are logging in. Here is a query that gets the data:

[font="Courier New"]SELECT

GT.loginname,

MIN(GT.StartTime) AS FirstLogin,

MAX(GT.StartTime) AS LastLogin

FROM

sys.traces T CROSS Apply

::fn_trace_gettable(T.path, 5) GT

WHERE

GT.loginname IS NOT NULL

GROUP BY

GT.LoginName

[/font]

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Robert Phillips-225223 Valued Member Points: 66 More actions · Answer 2

The users will login to SQL server with their windows account Domain\Username but in SQL we may only have windows groups setup (i.e. Domain\Domain Users, Domain\Domain Admins, Domain\Power users, etc.) and I need to know which of these group is being used. I'm trying to find out if I remove for example Domain\Power users group from SQL will I affect any users that may be getting authenticated via this group.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

I don't think that there is any default way within SQL Server to identify this as the SQL Server itself just knows the User Name not Domain Group that is logged in. After a little experimentation I think I found this query which uses the default trace in SQL Server 2005 to identify logins which are using a Group to access the SQL Server, it DOES NOT show the group, you would have to check AD to determine what group(s) the logins are part of:

[font="Courier New"]SELECT DISTINCT

I.NTUserName,

I.loginname,

I.SessionLoginName,

I.databasename,

S.*

FROM

sys.traces T CROSS Apply

::fn_trace_gettable(T.path, 5) I LEFT JOIN

sys.syslogins S ON

CONVERT(VARBINARY(MAX), I.loginsid) = S.sid

WHERE

S.sid IS NULL

[/font]

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Jack Corbett (9/4/2008)
[font="Courier New"]SELECT GT.loginname, MIN(GT.StartTime) AS FirstLogin, MAX(GT.StartTime) AS LastLogin FROM sys.traces T CROSS Apply ::fn_trace_gettable(T.path, 5) GT WHERE GT.loginname IS NOT NULL GROUP BY GT.LoginName
[/font]

This is seriously cool, Jack. I think that I'm going to steal this... 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Glad you like it Barry.

I have been doing a lot of research into Profiler and Tracing which is why many of my answers now include using that information.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

SQLRockstar SSCertifiable Points: 7014 More actions · Answer 6

Robert Phillips (9/4/2008)
The users will login to SQL server with their windows account Domain\Username but in SQL we may only have windows groups setup (i.e. Domain\Domain Users, Domain\Domain Admins, Domain\Power users, etc.) and I need to know which of these group is being used. I'm trying to find out if I remove for example Domain\Power users group from SQL will I affect any users that may be getting authenticated via this group.

my understanding is that if the domain user is a member of multiple groups, there is no way of knowing which group the user will be a member of when authenticated. this is the reason why you cannot assign a default schema to a windows group, because the user could be in more than one group and therefore would end up with a (possible) different default schema.

we actually had this problem when we assigned default databases to windows groups. every time a person or service account logged into an instance it would be in a different database (or so it seemed).

anyway, i have never seen a way to find out what group the login is a member of, but you would think there should be a system proc that would determine this info. if i find one, i will let you know.

----------------------
https://thomaslarock.com