This site is currently using a generated translation

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
SELECT FROM sys.server_principals p WHERE p.type = 'G' AND NOT LIKE 'NT SERVICE%'
WHERE name IN (SELECT name FROM @inValidGroups)
            SELECT TOP 1 @groupName = name FROM @groups
            INSERT INTO @members
            EXEC master.sys.xp_logininfo @groupName,'members
            DELETE FROM @groups WHERE name = @groupName
SELECT * FROM @members
  • Control BuiltinAdministrators
SELECT AS Roll, 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 = '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
            CASE WHEN = '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)

Then you just have to start going through everything...