usp_audit_passwords - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

usp_audit_passwords

Author

Topic

TimS
Posting Yak Master

198 Posts

Posted - 2005-07-22 : 19:06:57

SET QUOTED_IDENTIFIER ON; SET ANSI_NULLS ON
GO

IF OBJECT_ID('dbo.usp_audit_passwords') IS NULL
EXEC ('CREATE PROC dbo.usp_audit_passwords
AS RAISERROR ( ''Dummy Proc Called'', 16, 62)')
GO

ALTER PROC dbo.usp_audit_passwords AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#password') IS NULL
BEGIN
CREATE TABLE #password(
password nvarchar(128) NOT NULL PRIMARY KEY
);
END;

INSERT INTO #password (password)
SELECT src.password
FROM
(
SELECT 'password' UNION ALL
SELECT 'none'
) AS src (password)
LEFT JOIN #password dest ON src.password = dest.password
WHERE dest.password IS NULL

INSERT INTO #password (password)
SELECT DISTINCT l.name
FROM master.dbo.sysxlogins l
WHERE l.name IS NOT NULL AND NOT ((l.xstatus&4) = 4) -- NOT isntname

SELECT l.name, p.password
FROM master.dbo.sysxlogins l
JOIN #password p ON 1 = PWDCOMPARE(p.password, l.password, (CASE WHEN l.xstatus&2048=2048 THEN 1 ELSE 0 END))

END

/***

IF OBJECT_ID('tempdb..#password') IS NOT NULL DROP TABLE #password

CREATE TABLE #password(
password nvarchar(128) NOT NULL PRIMARY KEY
);

INSERT INTO #password (password) VALUES ('checkthispassword')

EXEC dbo.usp_audit_passwords

***/

GO

TimS
Posting Yak Master

198 Posts

Posted - 2005-07-22 : 19:09:14

I was reading this thread and thought I could do better; http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=6&messageid=203031&post=true

Tim S

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources