IF OBJECT_ID('dbo.sp__passwordcompliance') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.sp__passwordcompliance
    IF OBJECT_ID('dbo.sp__passwordcompliance') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp__passwordcompliance >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.sp__passwordcompliance >>>'
END
go


/*
-- USAGE :- 
----------
-- Proc written my Manish Negandhi on  to generate a report of logins who are not in compliance with below mentioned 3 
   attributes


--------------------------------------------------------------
Parameters :- 
 -- (a) password expiration
 -- (b) minimum password length 
 -- (c) maximum failed logins
    (d) if given as "Y" , modify and reset the value of above attributes otherwise only display

Examples :-
--The proc will list all the logins with above 3 attributes if parameters are passed as -1
sp__passwordcompliance -1,-1,-1,null	  ---> List all logins with a,b and c attributes given in parameters
sp__passwordcompliance 102,null,null,null ---> List all logins not having password expiration as 102
sp__passwordcompliance null,5,null,null   ---> List all logins not having mininum password length as 5
sp__passwordcompliance null,null,8,null   ---> List all logins not having max failed logins as 8

sp__passwordcompliance 102,null,null,"Y"  ---> List and MODIFY all logins not having password expiration as 102 

sp__passwordcompliance 102,5,null,"Y"     ---> List and MODIFY all logins not having 
					           --password expiration as 102 
                                                   --minimum password length as 5
                                                
sp__passwordcompliance 102,5,8,null       ---> List all logins not having 
					           --password expiration as 102
                       				   --minimum password length as 5
					           --max failed logins as 8
                     					       
sp__passwordcompliance 102,5,8,"Y"        ---> List and MODIFY all logins not having 
					            --password expiration as 102 
					            --minimum password length as 5
					            --max failed logins as 8
*/


create proc sp__passwordcompliance

@passwd_expiration int,
@min_passwd_length int,
@max_failed_logins int,
@modify_Y_or_N char(1)

as

declare 

@exec_passwd_expiration varchar(1000),
@exec_min_passwd_length varchar(1000),
@exec_max_failed_logins varchar(1000),
@suid int





set nocount on

SELECT suid,0 as passwedexp,0 as minpwdlen,0 as maxfailedlogins,"N" as flag into #logininfo 
from master..syslogins where suid > 2  order by suid

SELECT suid=object,passwdexp = int_value into #passwedexp from
master.dbo.sysattributes where class = 14 AND attribute = 0  AND object_cinfo =
"login"


SELECT suid=object,minpwdlen = int_value into #minpwdlen from
master.dbo.sysattributes where class = 14 AND attribute = 1 AND object_cinfo =
"login"


SELECT suid=object,maxfailedlogins = int_value into #maxfailedlogins from
master.dbo.sysattributes where class = 14 AND attribute = 2  AND object_cinfo =
"login"


update #logininfo 
set A.passwedexp=B.passwdexp
from #logininfo A,#passwedexp B
where A.suid=B.suid

update #logininfo 
set A.minpwdlen=B.minpwdlen
from #logininfo A,#minpwdlen B
where A.suid=B.suid


update #logininfo 
set A.maxfailedlogins=B.maxfailedlogins
from #logininfo A,#maxfailedlogins B
where A.suid=B.suid



if ( @passwd_expiration >= 0 or @passwd_expiration = -1 ) 
BEGIN
SELECT "LIST OF LOGINS NOT HAVING PASSWORD EXPIRATION SET AS ", @passwd_expiration
SELECT "================================================="
SELECT " "
SELECT suid,name=suser_name(suid),passwedexp as passwedexp from #logininfo where passwedexp  <> @passwd_expiration and suid > 

2 order by suid
SELECT " "
SELECT " "
END

if ( @min_passwd_length >= 0 or @min_passwd_length = -1  )
BEGIN
SELECT "LIST OF LOGINS NOT HAVING MINIMUM PASSWORD LENGTH SET AS ", @min_passwd_length
SELECT "====================================================="
SELECT " "
SELECT suid,name=suser_name(suid),minpwdlen as minpwdlen from #logininfo where minpwdlen <> @min_passwd_length and suid > 2 

order by suid
SELECT " "
SELECT " "
END

if (@max_failed_logins >= 0 or @max_failed_logins = -1 )
BEGIN
SELECT "LIST OF LOGINS NOT HAVING MAX FAILED LOGINS SET AS ",@max_failed_logins
SELECT "==============================================="
SELECT " "
SELECT suid,name=suser_name(suid),maxfailedlogins as maxfailedlogins from #logininfo where maxfailedlogins <> 

@max_failed_logins and suid > 2 order by suid
SELECT " "
SELECT " "
END
 
------------------------SECTION - 1 -----------------

IF @modify_Y_or_N IN ("y", "Y") 

BEGIN
	if ( @passwd_expiration >= 0  ) 
	BEGIN
   	PRINT "Starting modification for password expiration"
  	update #logininfo  set flag="N" -- reset flag to Y because we are in second section
	WHILE (1=1)
	BEGIN
	select top 1 @suid=suid from #logininfo where flag="N" and passwedexp != @passwd_expiration order by suid
	IF @@rowcount < 1 
	break
	else
	
	select @exec_passwd_expiration ="exec sp_modifylogin " + suser_name(@suid) + "," + "'passwd expiration'," + "'" + 	
	convert(varchar(10),@passwd_expiration) + "'" from  #logininfo where  passwedexp != @passwd_expiration  and 		

	suid=@suid 
	
	select @exec_passwd_expiration 
	exec (@exec_passwd_expiration)
	update #logininfo  set flag="Y" where suid=@suid
	END --- END of inner While loop
	print " "
	END --- END of outer while loop
END                                     

------------------------SECTION - 2 ----------------------------------------------------------------------


IF @modify_Y_or_N IN ("y", "Y")  -- this needs to be removed later on


BEGIN
	

	if ( @min_passwd_length >= 0 ) 
	BEGIN
   	PRINT "Starting modification for min password length"
   	update #logininfo  set flag="N" -- reset flag to Y because we are in second section
	WHILE (1=1)
	BEGIN
	select top 1 @suid=suid from #logininfo where flag="N" and minpwdlen != @min_passwd_length order by suid
	IF @@rowcount < 1 
	break
		else

	select @exec_min_passwd_length ="exec sp_modifylogin " + suser_name(@suid) + "," + "'min passwd length'," + "'" + 	

	convert(varchar(10),@min_passwd_length) + "'" from  #logininfo where  minpwdlen != @min_passwd_length  and suid=@suid 
	
	select @exec_min_passwd_length 
	exec (@exec_min_passwd_length)
	update #logininfo  set flag="Y" where suid=@suid
	END --- END of inner While loop
	print " "
	END --- END of outer while loop

END                                                                       


                                  


------------------------SECTION - 3 -----------------------------------


IF @modify_Y_or_N IN ("y", "Y") 

BEGIN

	if ( @max_failed_logins >= 0 ) 
	BEGIN
   	update #logininfo  set flag="N" -- reset flag to Y because we are in third section
    PRINT "Starting modification for password expiration"
	WHILE (1=1)
	BEGIN
	select top 1 @suid=suid from #logininfo where flag="N" and maxfailedlogins != @max_failed_logins order by suid
	IF @@rowcount < 1 
	break
	else
	
	select @exec_max_failed_logins ="exec sp_modifylogin " + suser_name(@suid) + "," + "'max failed_logins'," + "'" + 	

	convert(varchar(10),@max_failed_logins) + "'" from  #logininfo where  maxfailedlogins != @max_failed_logins  and 	

		suid=@suid 
	
	select @exec_max_failed_logins 
	exec (@exec_max_failed_logins)
	update #logininfo  set flag="Y" where suid=@suid
	END --- END of inner While loop
	print " "
	END --- END of outer while loop


END

go
EXEC sp_procxmode 'dbo.sp__passwordcompliance','unchained'
go
IF OBJECT_ID('dbo.sp__passwordcompliance') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.sp__passwordcompliance >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.sp__passwordcompliance >>>'
go