sql server - How do I monitor a subset of logins with sysadmin role to find out if they are still in use? -

How do I see a fixed sub-logon of log-in, to find out that they are still in use I would like to remove them, but they should not run the risk of deactivation if used.

I got the following code on mssqltips.com which works almost, but this test generates about 5 million records a day on the server and I do not know why anyone has another solution Or know how to fix this code, it will only be hosted when the system logs in with sysadm role?

  Make Table Tabo. Auditis EdinLogin (AuditEndIntInt Identity (1,1) No, Event Time Datetam Null, ServerLogin NYAARAAR (100) Faucet, Contract PK_ Audit SysAdminOologin Primary Key Clustered (Audit AventIDIDID)); Go Master Master; To create a trigger, start on logon at triglogon_heckForSysAdmin if you start (sysserver_role_members srm from sysserver_role_members srm srm.member_principal_id = sp.principal_id WHERE role_trustion_id = (SELECTSERVER_principals WHERE NAME = Sysadmin ') and sysserver_role_members srm from JOIN sys.server_principals sp sr Enter ORIGINAL_LOGIN () = sp.NAME) DBAWork.dbo.AuditSysAdminLogin (Event Time, Server Login) Value (GETDATE (), ORIGINAL_LOGIN ()) end; End; Your script sees that it will work as a design. GO  

Looks like you have In reality, Sisdomin accounts for 5 lakh times per day. If you do not need to enter every single login, and you are okay with accumulating recent login time, then you can change your trigger to merge as follows (table by applying this First must be chipped):

  Using Merge AuditSysAdminLogin AS (selections from SP Principal_ID) sys.server_role_members srm JOIN sys.server_principals sp sr srm.member_principal_id = sp.principal_id WHERE ROLE_principal_id = (SELECTSERVER_principals From SELECT principal_id WHERE NAME = 'Sysadmin') and new as ORIGINAL_LOGIN () = sp.NAME) Login.Principal_Id = NewLogin.ServerLogin on audit when the audit set is updated. Eventime = GETDATE () when INSERT (ServerLogin, EventTime) values ​​(Principal_ID, GETDATE) ());  

If you want to capture the number of logins, you can also add a BIGINT field and increase the merge statement.

Note - Running this type of trigger can have a dramatic effect on the performance of your SQL Server. I do not recommend running it for a long time.