January 23, 2011 at 7:56 am
Hi Folks,
I am SQL SERVER 2008 sp1. I need find and delete a login who did not login in to sql server for 120 days.
Any clue guys ?
April 11, 2011 at 2:07 pm
Hi,
First, you must make sure that you have [Login Auditing] turned at least for "Successful logins".
If you do not, then run the script below to turn it for for "Both Failed and Successful Logins":
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 3
GO
After that,
you can run the following query to retrieve all accounts that have not logged on or tried to login in at least 120 days:
Once you retrieve them, you can write code to disable them.
This code reads the error log for Logon Activities.
DECLARE @ErrorTable TABLE
(
LogDate DATETIME ,
ProcessInfo SYSNAME ,
[Text] VARCHAR(MAX)
) ;
INSERT INTO @ErrorTable
EXEC xp_readerrorlog ;
SELECT *
FROM sys.syslogins
WHERE name NOT IN (
SELECT SUBSTRING([Text], CHARINDEX(CHAR(39), [Text], 0) + 1,
( CHARINDEX(CHAR(39), [Text],
CHARINDEX(CHAR(39), [Text], 0) + 1)
- CHARINDEX(CHAR(39), [Text], 0) ) - 1)
FROM @ErrorTable
WHERE ProcessInfo = 'Logon'
AND LogDate > DATEADD(DAY, -120, GETDATE()) )
QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply