Testing logins and passwords from a stored procedure

-- This is required to connect back to the local server
If Not Exists (Select Top 1 0 From sys.configurations Where name = 'Ad Hoc Distributed Queries' And value = 1)
Begin
	Exec sp_configure 'Ad Hoc Distributed Queries', '1'
	Reconfigure
End

Set Nocount On

If Not Exists (Select Top 1 0 From sys.databases Where name = 'UserDatabase')
	Create Database UserDatabase
Go

Use UserDatabase
Go

If Exists (Select Top 1 0 From sys.tables Where name = 'UserList')
	Drop Table dbo.UserList
Go

Create Table dbo.UserList (
	UserId Int Identity(1,1),
	UserName Nvarchar(Max),
	Password nvarchar(Max)
	)
Go

-- These will be our test subjects
Insert	dbo.UserList (UserName, Password)
Select	'frodo', 'baggins'
Insert	dbo.UserList (UserName, Password)
Select	'gandalf', 'white'
Insert	dbo.UserList (UserName, Password)
Select	'katniss', 'everdeen'
Insert	dbo.UserList (UserName, Password)
Select	'harry', 'potter'
Go

If Exists (Select Top 1 0 From sys.syslogins Where name = 'frodo')
	Drop Login frodo
If Exists (Select Top 1 0 From sys.syslogins Where name = 'gandalf')
	Drop Login gandalf
If Exists (Select Top 1 0 From sys.syslogins Where name = 'katniss')
	Drop Login katniss
If Exists (Select Top 1 0 From sys.syslogins Where name = 'harry')
	Drop Login harry
Go

-- One login with the correct password
Create Login frodo With Password = 'baggins', Check_policy = Off, Check_expiration = Off
-- One login with an incorrect password, he changed from grey to white
Create Login gandalf With Password = 'grey', Check_policy = Off, Check_expiration = Off
-- One login where you can't fix the password as it (probably) won't be complex enough
Create Login katniss With Password = 'everdean', Check_policy = Off, Check_expiration = Off
Alter Login katniss With Check_policy = On
-- And one login that is disabled, and so can't login, no matter what you do with the password
Create Login harry With Password = 'potter', Check_policy = Off, Check_expiration = Off
Go
Alter Login harry Disable
Go

If Exists (Select Top 1 0 From sys.procedures Where name = 'Check_Passwords')
	Drop Procedure dbo.Check_Passwords
Go

Create Procedure dbo.Check_Passwords
	@Print_Debug Bit = 0
As
Begin
	Set Nocount On

	Declare	@Command nvarchar(Max), @UserName nvarchar(Max), @Password Nvarchar(Max), @FirstRun Bit = 1

	-- Scroll is an important part of our strategy
	Declare UserList Cursor Scroll For
		Select	UserName As UserName,
			Password As Password
		From	UserList

	Open	UserList
	Fetch Next From UserList Into @UserName, @Password
	While	@@fetch_status = 0
	Begin
		-- This is what we use to test the login
		Set		@Command = 'Set Nocount On; Select Top 1 0 As Test Into #TempTable From Openrowset(''SQLOLEDB'', ' + QuoteName(@@servername, '''') + '; ' + QuoteName(@UserName, '''') + '; ' + QuoteName(@Password, '''') + ', ''Set FmtOnly Off; Select @@version Version'') A'
		Begin Try
			If		@Print_Debug = 1
					Print	@Command
			Exec	sys.sp_executesql @Command

			Print	'Login ' + @UserName + ' succeeded'
			Set		@FirstRun = 1
		End Try
		Begin Catch
			If		@FirstRun = 1
			Begin
					-- If it failed on our first attempt, try to fix it
					Set	@Command = 'Alter Login ' + QuoteName(@UserName) + ' With Password = ' + QuoteName(@Password, '''')
					Begin Try
						If	@Print_Debug = 1
							Print	@Command
						Exec	sys.sp_executesql @Command
						Print	'Login ' + @UserName + ' failed and the password has been reset. Re-testing.'

						-- This resets the cursor so we'll get this same record again to test it
						Fetch Prior From UserList Into @UserName, @password
						Set	@FirstRun = 0
					End Try
					Begin Catch
						-- Sometimes we might not be able to alter it for some reason
						Print	'Login ' + @UserName + ' failed and the password could not be reset.'
						Set	@FirstRun = 1
					End Catch
			End
			Else
			Begin
					-- If we previously tried to fix it, give up and move on
					Print	'Login ' + @UserName + ' failed even after the password was reset.'
					Set	@FirstRun = 1
			End
		End Catch

		Fetch Next From UserList Into @UserName, @Password
	End

	Close UserList
	Deallocate UserList
End
Go

-- Run the test
Exec	dbo.Check_Passwords