How to Find who created Logins on SQL server for compliance request

Question

_{Tuesday, July 18, 2017 9:23 AM}

Hi Team,

We have a compliance request where they are asking to find who has created a specific login on SQL server. I have tried checking Logs but no luck. We dont have traces to find the details. Looking forward for your quick responses on this as this is raised as a priority issue.

Regards,

Rizwan

All replies (7)

_{Tuesday, July 18, 2017 10:05 AM}

Hello,

SQL Server don't log who created which account.

Olaf Helper

[ Blog] [ Xing] [ MVP]

---

_{Tuesday, July 18, 2017 11:02 AM}

SQL Server does't keep track of who created Logins/Users. However you can still try to query default trace and look for EventClass 109.

---

_{Tuesday, July 18, 2017 12:23 PM}

As others said, SQL Server doesn't give out that information directly but if the login was created pretty recently, you may be successful in finding who created it by querying the default trace but note that if your sever is busy, chances are that default trace may have been overwritten/rolled over already so that information may not be there but it won't cost you anything to double check. 

Check this link out and read the part where it says "Security Audit Events"

SELECT  TE.name AS [EventName] ,
        v.subclass_name ,
        T.DatabaseName ,
        t.DatabaseID ,
        t.NTDomainName ,
        t.ApplicationName ,
        t.LoginName ,
        t.SPID ,
        t.StartTime ,
        t.RoleName ,
        t.TargetUserName ,
        t.TargetLoginName ,
        t.SessionLoginName
FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1
                                                              f.[value]
                                                      FROM    sys.fn_trace_getinfo(NULL) f
                                                      WHERE   f.property = 2
                                                    )), DEFAULT) T
        JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
        JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id
                                            AND v.subclass_value = t.EventSubClass
WHERE   te.name IN ( 'Audit Addlogin Event', 'Audit Add DB User Event',
                     'Audit Add Member to DB Role Event' )
        AND v.subclass_name IN ( 'add', 'Grant database access' )

Please click Mark As Answer if my response answered your question or vote as helpful if it helped you in any way

---

_{Tuesday, July 18, 2017 3:38 PM}

I tried your query but result set is blank. Can anyone please provide how to gather these details

---

_{Tuesday, July 18, 2017 3:44 PM}

Hi Olaf,

Is there anyway we can convey to compliance team about no logging of login creator in SQL server? Is there a way we can log this activity into SQL sever or errorlogs?

Regards,

Rizwan

---

_{Tuesday, July 18, 2017 4:03 PM}

Hello Rizwan,

How to convey what's not done by SQL Server? MS documents only existing function, not none-existing ones.

The only little on information is who granted database level permssions for logins, see List all Database Permissions => Grantor

Olaf Helper

[ Blog] [ Xing] [ MVP]

---

_{Tuesday, July 18, 2017 9:12 PM}

I tried your query but result set is blank. Can anyone please provide how to gather these details

So the answer is already given to you: this information is nowhere to be found when it comes to existing logins.

However, you could set up an audit so that this is audited in the future.