Its important for security to monitor unused logins on SQL. Only logins which need to be there should be there. There are a number of ways to monitor the which logins are using used but these methods vary across the versions of SQL, it can be handy to have a basic standard method which a DBA is confidence possesses interoperability across all versions of SQL. Below i’ve outlined a really simple way the sys.sysprocesses table can be used to capture logon data which could be used to find logins which aren’t being used

1) Create a table to hold the audit data

create table record_login_user_behaviour (

loginame         varchar(50 ),
cmd              varchar(200 ),
database_name    varchar(50 ),
frequency        int,
date_captured    datetime

)

2) Create a stored procedure to capture login names of currently running processes. I included a count of the commands being used and the time, you may want to tweak for your needs.

USE <<database>>
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure mon_record_login_user_behaviour

as
set nocount on

insert into record_login_user_behaviour
select distinct loginame,cmd , DB_NAME (DBID) as database_name , COUNT(loginame ) as frequency, GETDATE() as date_captured
from sys .sysprocesses
where loginame <> 'sa'
group by loginame, cmd , dbid
GO

3) Attach capture procedure to job to regularly grab what logins are being used.

4) The script below will compare what logins have been captured as being used against the logins on SQL and produces a list of what what logins haven’t been used.

select distinct name
from sys .server_permissions as spe
inner join sys. server_principals as spr
on spe. grantee_principal_id  = spr .principal_id
where name not in (select distinct loginame from record_login_user_behaviour )

Any ways of improving this activity would be gratefully recieved :-)