SQL Server Security - Login Audit Part 2

Manually revising the login of an instance is time consuming. The collection of data can mostly be done via scripting, it simplifies some but the analysis has to be done manually. Often several teams and stakeholders have to be involved in the work e.g. Windows team and security group owners

Members in server roles

EXEC master.sys.sp_helpsrvrolemember

Validate all Windows logins

EXEC master.sys.sp_validatelogins

Lists all Windows logins that no longer exist on the server or in Active Directory.

List of Windows groups with members

DECLARE @members table(accountName nvarchar(255), type nvarchar(55), privilege nvarchar(55), mappedLoginName nvarchar(255), permissionPath nvarchar(255))
DECLARE @groups table(name nvarchar(255))
DECLARE @inValidGroups table(SID uniqueidentifier, name nvarchar(255))
DECLARE @groupName nvarchar(255)

INSERT INTO @inValidGroups
EXEC master.sys.sp_validatelogins
INSERT INTO @groups
SELECT p.name FROM sys.server_principals p WHERE p.type = 'G' AND p.name NOT LIKE 'NT SERVICE%'
DELETE FROM @groups
WHERE name IN (SELECT name FROM @inValidGroups)
WHILE EXISTS (SELECT 1 FROM @groups)
BEGIN
            SELECT TOP 1 @groupName = name FROM @groups
            INSERT INTO @members
            EXEC master.sys.xp_logininfo @groupName,'members
            DELETE FROM @groups WHERE name = @groupName
END
SELECT * FROM @members

Control BuiltinAdministrators

SELECT r.name AS Roll, u.name AS loginName
FROM sys.server_role_members m JOIN
sys.server_principals r ON m.role_principal_id = r.principal_id JOIN
sys.server_principals u ON m.member_principal_id = u.principal_id
WHERE u.name = 'BUILTINAdministrators'

If the group has rights on the instance, they are shown in the list above.

List and control users in local administrator
Normally this is outside our responsibility and is controlled by those responsible for the OS but it can be done manually or via e.g. PowerShell scripts.
Check the status of sa

SELECT p.name, 
            CASE WHEN p.name = 'sa' THEN 'No' ELSE 'Yes' END [Changed name], 
            CASE WHEN p.is_disabled = 0 THEN 'Active' ELSE 'Not Active' END AS [Status]
FROM sys.server_principals AS p
WHERE p.sid = 0x01 
AND p.type = 'S'

Check password requirements for all SQL logins

SELECT name FROM sys.sql_logins 
WHERE is_policy_checked=0

List SQL logins that have weak passwords

To check which SQL logins have weak passwords we use the script below. It is based on a list of weak passwords, the ones in the list below are the ones we usually encounter and can be extended if you want.

     DECLARE @weakPwdList nvarchar(155)
SET @weakPwdList = '123,1234,12345,abc,default,guest,123456,|Login|123,|Login|,|Login||Login|,admin,Administrator,admin123,,'
SELECT name AS [Login att kontrollera], 
CASE WHEN PWDCOMPARE(REPLACE(wPwd.pwd,'|Login|',REVERSE(name)),password_hash) = 0 THEN REPLACE(wPwd.pwd,'|Login|',name) 
            ELSE REPLACE(wPwd.pwd,'|Login|',REVERSE(name))
END AS [Svagt lösenord]
FROM sys.sql_logins INNER JOIN
( 
            SELECT SUBSTRING(@weakPwdList, [number]+1, CHARINDEX(',', @weakPwdList, [number]+1)-[number]-1) AS pwd
            FROM master.dbo.spt_values s
            WHERE s.type='P'
            AND s.Number < LEN(@weakPwdList)
            AND SUBSTRING(@weakPwdList, [number], 1) = ','
) AS wPwd ON (PWDCOMPARE(wPwd.pwd, password_hash) = 1 OR
PWDCOMPARE(REPLACE(wPwd.pwd,'|Login|',name),password_hash) = 1 OR
PWDCOMPARE(REPLACE(wPwd.pwd,'|Login|',REVERSE(name)),password_hash) = 1)
ORDER BY name

Then you just have to start going through everything...
/Björn