Auditing Your SQL Server Environment Part I

IF OBJECT_ID(‘dbo.spAuditPasswords’) IS NOT NULL
DROP PROCEDURE dbo.spAuditPasswords
GO

CREATE PROCEDURE dbo.spAuditPasswords
AS
/*******************************************************************
Creation Date: 03/22/02 Created By: Randy Dyess
Web Site: www.TransactSQL.Com
Email: RandyDyess@TransactSQL.Com
Purpose: Perform a simple audit of user’s passwords
Location: master database
Output Parameters: None
Return Status: None
Called By: None
Calls: None
Data Modifications: None
Updates: None
Date Author Purpose
———- ————————– —————————-
*******************************************************************/
SET NOCOUNT ON

–Variables
DECLARE @lngCounter INTEGER
DECLARE @lngCounter1 INTEGER
DECLARE @lngLogCount INTEGER
DECLARE @strName VARCHAR(256)

–Create table to hold SQL logins
CREATE TABLE #tLogins
(
numID INTEGER IDENTITY(1,1)
,strLogin SYSNAME NULL
,lngPass INTEGER NULL
)

–Insert non ntuser into temp table
INSERT INTO #tLogins (strLogin)
SELECT name FROM master.dbo.syslogins WHERE isntname = 0
SET @lngLogCount = @@ROWCOUNT

–Determine if password is null and user is SQL Login
PRINT ‘The following logins have blank passwords’
SELECT name AS ‘Login Name’ FROM master.dbo.syslogins
WHERE password IS NULL
AND isntname = 0


–Determine if password and name are the same
SET @lngCounter = @lngLogCount

WHILE @lngCounter <> 0
BEGIN
SET @strName = (SELECT strLogin FROM #tLogins WHERE numID = @lngCounter)

UPDATE #tLogins
SET lngPass = (SELECT PWDCOMPARE (@strName,(SELECT password FROM master.dbo.sysxlogins WHERE name = @strName)))
WHERE numID = @lngCounter

SET @lngCounter = @lngCounter – 1
END

PRINT ‘The following logins have passwords the same as their login name’
SELECT strLogin AS ‘Login Name’ FROM #tLogins WHERE lngPass = 1

–Reset column for next password test
UPDATE #tLogins
SET lngPass = 0

–Determine if password is only one character long
SET @lngCounter = @lngLogCount

WHILE @lngCounter <> 0
BEGIN
SET @lngCounter1 = 1
SET @strName = (SELECT strLogin FROM #tLogins WHERE numID = @lngCounter)
WHILE @lngCounter1 < 256
BEGIN
UPDATE #tLogins
SET lngPass = (SELECT PWDCOMPARE (CHAR(@lngCounter1),(SELECT password FROM master.dbo.sysxlogins WHERE name = @strName)))
WHERE numID = @lngCounter
AND lngPass <> 1

SET @lngCounter1 = @lngCounter1 + 1
END

SET @lngCounter = @lngCounter – 1
END

PRINT ‘The following logins have one character passwords’
SELECT strLogin AS ‘Login Name’ FROM #tLogins WHERE lngPass = 1
GO

–Test
EXEC dbo.spAuditPasswords
GO

 

Continues…

Pages: 1 2 3 4




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |