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
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
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
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
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
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