How To Check Who Created Login In Sql Server

Remove From My Forums

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

Question
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

Hello,

SQL Server don't log who created which account.

Olaf Helper

[ Blog] [ Xing] [ MVP]
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.

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

I tried your query but result set is blank. Can anyone please provide how to gather these details
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
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]
- Proposed as answer by Wednesday, July 19, 2017 1:41 AM
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.

How To Check Who Created Login In Sql Server

Source: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8c190713-231f-42d1-83cf-9b0cdb3b712b/how-to-find-who-created-logins-on-sql-server-for-compliance-request?forum=sqldataaccess#:~:text=SQL%20Server%20does't%20keep,and%20look%20for%20EventClass%20109.

Posted by: juarezroake1984.blogspot.com