October 9, 2011 at 9:15 pm
I have a table tblUserAccounts. This has the following fields
UserId int,
UserName varchar(50),
Password varchar(20),
ValidUpto datetime
This contains approx 50K records. Earlier there was no password policy set in the organization. Now they are trying to implement certain rules. So I need to figure out the records whose password doesn't comply with the rules. The proposed rules are
1. It should contain at least a number
2. Should be of 10 char lenght
3. Should contain one or more special characters
4. Should start with an alphabet
What should be the best way to do this ? How can I do this with a single query ?
October 10, 2011 at 4:15 am
sanujss (10/9/2011)
I have a table tblUserAccounts. This has the following fieldsUserId int,
UserName varchar(50),
Password varchar(20),
ValidUpto datetime
This contains approx 50K records. Earlier there was no password policy set in the organization. Now they are trying to implement certain rules. So I need to figure out the records whose password doesn't comply with the rules. The proposed rules are
1. It should contain at least a number
2. Should be of 10 char lenght
3. Should contain one or more special characters
4. Should start with an alphabet
What should be the best way to do this ? How can I do this with a single query ?
Sample data to play around with: -
--First, lets create some sample data to play with
IF object_id('tempdb..#tblUserAccounts') IS NOT NULL
BEGIN
DROP TABLE #tblUserAccounts
END
--50,000 Random rows of data
SELECT TOP 50000 IDENTITY(INT,1,1) AS UserId, LEFT(CONVERT(VARCHAR(255), NEWID()),50) AS UserName,
SUBSTRING(CONVERT(VARCHAR(255), NEWID()), 1, ABS(CAST(NEWID() AS binary(6)) % 25) + 1) AS [PassWord],
DATEADD(DAY, 0, DATEDIFF(DAY, 0, DATEADD(DAY, ABS(CAST(NEWID() AS binary(6)) % 30) + 1, GETDATE()))) AS ValidUpto
INTO #tblUserAccounts
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2
--Add a Primary Key
ALTER TABLE #tblUserAccounts
ADD CONSTRAINT tblUserAccounts_PK_UserId
PRIMARY KEY CLUSTERED (UserId) WITH FILLFACTOR = 100
Now, lets look at creating your query! 🙂
--View Invalid PassWords
SELECT mainTable.*
FROM #tblUserAccounts mainTable
INNER JOIN (SELECT UserId, PATINDEX('%[0-9]%',[PassWord]) AS rule1,
PATINDEX('%[^A-Za-z0-9]%',[PassWord]) AS rule3,
PATINDEX('[A-Za-z]%',[PassWord]) AS rule4
FROM #tblUserAccounts) rules ON mainTable.UserId = rules.UserId
AND (rules.rule1 = 0 OR rules.rule3 = 0 OR rules.rule4 = 0) --Rules 1, 3 and 4
WHERE LEN(mainTable.[PassWord]) <> 10 --Rule 2
How's that?
October 10, 2011 at 4:48 am
That's great help ... Let me play with it and get back... Thanks a lot buddy... 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply