script pour vérifier la complexité des passwords

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

CREATE PROCEDURE dbo.spAuditPasswords
AS

SET NOCOUNT ON

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

-- Determine Database version in @ver
CREATE TABLE dbo.#SqlServerVersionTable (
unused1 INT,
unused2 CHAR(100),
unused3 CHAR(100),
version CHAR(100)
)
INSERT INTO #SqlServerVersionTable
EXEC master.dbo.xp_msver ProductVersion
DECLARE @VersionString CHAR(100)
DECLARE @dot INT
DECLARE @ver INT
SELECT TOP 1 @VersionString = version
FROM #SqlServerVersionTable
SELECT @dot = CHARINDEX('.', @VersionString)
SELECT @ver = CONVERT(INT, SUBSTRING(@VersionString, 1, @dot-1))

--Create table to hold SQL logins
CREATE TABLE #tLogins
(
numID INTEGER IDENTITY(1,1)
,strLogin nvarchar(100) NULL
,lngPass INTEGER NULL
,lngPass2 INTEGER NULL
,password nvarchar(10)
,ROLE_NAME nvarchar(100) NULL)


--Insert users into #tLogins
INSERT INTO #tLogins (strLogin)
SELECT name FROM master.dbo.syslogins
SET @lngLogCount = @@ROWCOUNT

--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
sys.sql_logins WHERE name = @strName)))
WHERE numID = @lngCounter

SET @lngCounter = @lngCounter - 1
END


--Reset column for next password test
UPDATE #tLogins SET lngPass2 = 0
update #tLogins set password = 'OK'

--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 lngPass2 = (SELECT PWDCOMPARE (CHAR(@lngCounter1),(SELECT password
FROM sys.sql_logins WHERE name = @strName)))
WHERE numID = @lngCounter
AND lngPass2 <> 1

SET @lngCounter1 = @lngCounter1 + 1
END

SET @lngCounter = @lngCounter - 1
END

/* Determine if :
- password and name are the same
- password is empty
- password is only one character long
*/
update #tLogins set password = 'A CHANGER' where (lngPass = 1 or lngPass is
NULL) or (lngPass2 = 1 or lngPass2 is NULL)
update #tLogins set password = 'OpSystem' where patindex('%%', strLogin) <> 0

-- Extract db_owner
set nocount on
declare @name nvarchar(128),
@SQL nvarchar(600)
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID
('tempdb..#tmpTable'))
drop table #tmpTable
CREATE TABLE #tmpTable (
DATABASENAME nvarchar(35) NOT NULL ,
USER_NAME nvarchar(40) NOT NULL,
ROLE_NAME nvarchar(10) NOT NULL)
declare c1 cursor for
select name from master.dbo.sysdatabases
open c1
fetch c1 into @name
while @@fetch_status >= 0
begin
select @SQL =
'insert into #tmpTable
select N'''+ @name + ''', a.name, c.name
from ' + QuoteName(@name) + '.dbo.sysusers a
join ' + QuoteName(@name) + '.dbo.sysmembers b on b.memberuid = a.uid
join ' + QuoteName(@name) + '.dbo.sysusers c on c.uid = b.groupuid
where a.name != ''dbo'' and c.name = ''db_owner'''
/* Insert row for each database */
execute (@SQL)
fetch c1 into @name
end
close c1
deallocate c1

-- Paste db_owner from #tmpTable to #tLogins
set nocount on
declare @owner nvarchar(128),
@SQL2 nvarchar(400)
declare c2 cursor for
select distinct USER_NAME from #tmpTable
open c2
fetch c2 into @owner
while @@fetch_status >= 0
begin
set @SQL2 = 'update #tLogins set ROLE_NAME = ''db_owner''
where right(strLogin, len(strLogin) - patindex(''%%'', strLogin)) =''' +
@owner + ''''
/* Insert row for each database */
EXEC sp_executesql @SQL2
fetch c2 into @owner
end
close c2
deallocate c2


-- Extract db_owner from sp_helpdb
set nocount on
declare @db_owner nvarchar(128),
@SQL3 nvarchar(400)

CREATE TABLE #tmp_Database(
database_name nvarchar(128),
database_size nvarchar(13) null,
owner nvarchar(128),
dbid smallint,
created nvarchar(11),
status nvarchar(600),
compatibility_level tinyint
)

IF (@ver <= 7)
BEGIN
INSERT INTO
#tmp_Database(database_name,database_size,owner,dbid,created,status)
EXEC sp_helpdb
END
ELSE
BEGIN
INSERT INTO
#tmp_Database(database_name,database_size,owner,dbid,created,status,compatibility_level)
EXEC sp_helpdb
END

declare c3 cursor for
select distinct owner from #tmp_Database
open c3
fetch c3 into @db_owner
while @@fetch_status >= 0
begin
set @SQL3 = 'update #tLogins set ROLE_NAME = ''db_owner''
where right(strLogin, len(strLogin) - patindex(''%%'', strLogin)) =''' +
@db_owner + ''''
exec sp_executesql @SQL3
fetch c3 into @db_owner
end
close c3
deallocate c3






-- Requete de sortie
select cast(@@servername as nvarchar(40)),strLogin,password,ROLE_NAME from
#tLogins

/* select distinct cast(@@servername as nvarchar(40)) as SERVER, a.strLogin
as LOGIN, a.password, b.ROLE_NAME
from #tLogins a LEFT JOIN #tmpTable b
on right(strLogin, len(a.strLogin) - patindex('%%', strLogin)) = b.USER_NAME
*/
GO

--Execution
EXEC dbo.spAuditPasswords

"Brigitte" wrote:

Bonjour,

Je suis à la recherche d'un script permettant de vérifier la complexité des
mots de passe des logins.

Merci

Bonne journée

Brigitte

On 22 jan, 15:14, Brigitte <Brigi...@discussions.microsoft.com> wrote:

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

CREATE PROCEDURE dbo.spAuditPasswords
AS

SET NOCOUNT ON

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

-- Determine Database version in @ver
CREATE TABLE dbo.#SqlServerVersionTable (
   unused1         INT,
   unused2         CHAR(100),
   unused3         CHAR(100),
   version         CHAR(100)
)
INSERT INTO #SqlServerVersionTable
    EXEC master.dbo.xp_msver ProductVersion
DECLARE @VersionString  CHAR(100)
DECLARE @dot            INT
DECLARE @ver            INT
SELECT  TOP 1 @VersionString = version
FROM    #SqlServerVersionTable
SELECT @dot = CHARINDEX('.', @VersionString)
SELECT @ver = CONVERT(INT, SUBSTRING(@VersionString, 1, @dot-1))

--Create table to hold SQL logins
CREATE TABLE #tLogins
(
numID INTEGER IDENTITY(1,1)
,strLogin nvarchar(100) NULL
,lngPass INTEGER NULL
,lngPass2 INTEGER NULL
,password nvarchar(10)
,ROLE_NAME nvarchar(100) NULL)

--Insert users into #tLogins
INSERT INTO #tLogins (strLogin)
        SELECT name FROM master.dbo.syslogins
SET @lngLogCount = @@ROWCOUNT

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

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

        UPDATE #tLogins
        SET lngPass = (SELECT PWDCOMPARE (@strName,(SELECT pass word FROM
sys.sql_logins WHERE name = @strName)))
        WHERE numID = @lngCounter

        SET @lngCounter = @lngCounter - 1
END

--Reset column for next password test
UPDATE #tLogins SET lngPass2 = 0
update #tLogins set password = 'OK'

--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 num ID = @lngCounter)
        WHILE @lngCounter1 < 256
        BEGIN
                UPDATE #tLogins
                SET lngPass2 = (SELECT PWDCOMPARE (CHAR (@lngCounter1),(SELECT password
FROM sys.sql_logins WHERE name = @strName)))
                WHERE numID = @lngCounter
                AND lngPass2 <> 1

                SET @lngCounter1 = @lngCounter1 + 1
        END

        SET @lngCounter = @lngCounter - 1
END

/* Determine if :
        - password and name are the same
        - password is empty
        - password is only one character long
*/
update #tLogins set password = 'A CHANGER' where (lngPass = 1 or lngP ass is
NULL) or (lngPass2 = 1 or lngPass2 is NULL)
update #tLogins set password = 'OpSystem' where patindex('%%', strLogi n) <> 0

-- Extract db_owner
set nocount on
declare @name nvarchar(128),
        @SQL  nvarchar(600)
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID
('tempdb..#tmpTable'))
        drop table #tmpTable
CREATE TABLE #tmpTable (
        DATABASENAME nvarchar(35) NOT NULL ,
        USER_NAME nvarchar(40) NOT NULL,
        ROLE_NAME nvarchar(10) NOT NULL)
declare c1 cursor for
        select name from master.dbo.sysdatabases
open c1
fetch c1 into @name
while @@fetch_status >= 0
begin
        select @SQL =
                'insert into #tmpTable
                 select N'''+ @name + ''', a.name, c.na me
                from ' + QuoteName(@name) + '.dbo.sysuser s a
                join ' + QuoteName(@name) + '.dbo.sysmemb ers b on b.memberuid = a.uid
                join ' + QuoteName(@name) + '.dbo.sysuser s c on c.uid = b.groupuid
                where a.name != ''dbo'' and c.name = ''db_owner'''
                /*      Insert row for each databas e */
                execute (@SQL)
        fetch c1 into @name
end
close c1
deallocate c1

-- Paste db_owner from #tmpTable to #tLogins
set nocount on
declare @owner nvarchar(128),
        @SQL2  nvarchar(400)
declare c2 cursor for
        select distinct USER_NAME from #tmpTable
open c2
fetch c2 into @owner
while @@fetch_status >= 0
begin
        set @SQL2 = 'update #tLogins set ROLE_NAME = ''db_own er''
                where right(strLogin, len(strLogin) - pat index(''%%'', strLogin)) =''' +
@owner + ''''
                /*      Insert row for each databas e */
        EXEC sp_executesql @SQL2
        fetch c2 into @owner
end
close c2
deallocate c2

-- Extract db_owner from sp_helpdb
set nocount on
declare @db_owner nvarchar(128),
        @SQL3  nvarchar(400)

CREATE TABLE #tmp_Database(
database_name nvarchar(128),
database_size nvarchar(13) null,
owner nvarchar(128),
dbid smallint,
created nvarchar(11),
status nvarchar(600),
compatibility_level tinyint
)

IF (@ver <= 7)
   BEGIN
        INSERT INTO
        #tmp_Database(database_name,database_size,owner,dbid,crea ted,status)
        EXEC sp_helpdb
   END
   ELSE
   BEGIN
        INSERT INTO
        #tmp_Database(database_name,database_size,owner,dbid,crea ted,status,compati bility_level)
        EXEC sp_helpdb
   END

declare c3 cursor for
        select distinct owner from #tmp_Database
open c3
fetch c3 into @db_owner
while @@fetch_status >= 0
begin
        set @SQL3 = 'update #tLogins set ROLE_NAME = ''db_own er''
                where right(strLogin, len(strLogin) - pat index(''%%'', strLogin)) =''' +
@db_owner + ''''
        exec sp_executesql @SQL3
        fetch c3 into @db_owner
end
close c3
deallocate c3

-- Requete de sortie
select cast(@@servername as nvarchar(40)),strLogin,password,ROLE_NAME fro m
#tLogins

/* select distinct cast(@@servername as nvarchar(40)) as SERVER, a.strLog in
as LOGIN, a.password, b.ROLE_NAME
        from #tLogins a LEFT JOIN #tmpTable b
        on right(strLogin, len(a.strLogin) - patindex('%%', strL ogin)) = b.USER_NAME
*/
GO

--Execution
EXEC dbo.spAuditPasswords

"Brigitte" wrote:
> Bonjour,

> Je suis à la recherche d'un script permettant de vérifier la comple xité des
> mots de passe des logins.

> Merci

> Bonne journée

> Brigitte

Grand merci pour ton code j'ai bien aimé, les truc pour éviter les
curseurs comme
Créer une table temporaire avec un identity puis parcourir while
@count<>@@lngCounter.
Mais je ne suis pas complètement convaincu pour les performances.


Ex :
INSERT INTO #tLogins (strLogin)
SELECT name FROM master.dbo.syslogins
SET @lngLogCount = @@ROWCOUNT
--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
sys.sql_logins WHERE name = @strName)))
WHERE numID = @lngCounter
SET @lngCounter = @lngCounter - 1
END