SQL Server Blog

How UNsafe Is Your SQL Server? – Vulnerability Assessment and Access Audit

SQL Server is a repository of critical data, attracting potential threats from malicious individuals seeking financial gains. As a SQL Server DBA, it is essential to assess the security vulnerabilities of your SQL Server and perform an access audit to ensure the safety of your data

SQL Server Vulnerability Scan

I recommend you start with the built-in SQL Server vulnerability assessment tool to identify and review potential vulnerabilities. While not all recommendations need to be implemented, it is important to consider each and document them in your disaster recovery plans’ “Accepted Risk” section. You can create a custom report using the provided scripts and run it across your environments to expedite the process. A helpful resource is the Microsoft Books walkthrough, which offers guidance through the tool.

Here is an example of one of the scripts provided in the Vulnerability assessment, modified and used on a Central Management Server:

Along with the General checks of the above vulnerability assessment, I would recommend spending the time needed to go through each of the below sections in your environment:

Logins

Verify the necessity of each login’s access to the server and review their explicit and role-based permissions. Follow the script below to identify all logins:

-- Windows Logins
SELECT * 
FROM sys.server_principals 
WHERE type IN ('U','G', 'E', 'X') 
AND name NOT LIKE '%NT%'
      	GO

-- SQL Logins 
       SELECT * 
FROM sys.server_principals 
WHERE type = 'S' 
AND name NOT LIKE '%#%' 
AND name NOT LIKE 'sa'
      	GO

Note: Windows integrated accounts are easier to manage and more secure. If possible, migrate users from SQL Authenticated accounts to Windows Authenticated accounts.

Explicitly granted permissions to Logins:

To find permissions explicitly granted to each login, execute the following scripts:

-- Server Logins with explicitly granted permissions
SELECT DISTINCT pr.principal_id
	, pr.name
	, pr.type_desc
	, pe.state_desc
	, pe.permission_name
	, so.name
	, so.type_desc
FROM sys.server_principals AS pr
JOIN sys.server_permissions AS pe
    ON pe.grantee_principal_id = pr.principal_id
INNER JOIN sys.all_objects so
	ON pe.major_id = so.object_id
WHERE pr.type IN ('U','G', 'E', 'X', 'S') 
	AND pr.name NOT LIKE '%NT%' 
	AND pr.name NOT LIKE '%##%' 
	AND permission_name not like '%Connect%'

-- Database Logins with explicitly granted permissions
EXEC sp_MSforeachdb 'USE ?
	SELECT DISTINCT db_name() as [Database name]
	, pr.principal_id
	, pr.name
	, pr.type_desc
	, pr.authentication_type_desc
	, pe.state_desc
	, pe.permission_name
	, so.name
	, so.type_desc
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
    ON pe.grantee_principal_id = pr.principal_id
INNER JOIN sys.all_objects so
	ON pe.major_id = so.object_id
	WHERE pr.type IN (''U'',''G'', ''E'', ''X'', ''S'') 
	AND pr.name NOT LIKE ''%NT%'' 
	AND pr.name NOT LIKE ''%##%'' 
	AND permission_name not like ''%Connect%'''

--Edited on 8/24/2023 to add WHAT object are referenced in the explicit permissions

Review the outputs of these scripts. If any unexpected permissions are returned, such as ‘CONTROL SERVER,’ address them promptly. Ideally, explicit permissions should be primarily in the ‘DENY’ realm, with roles being the preferred method of managing and assigning permissions.

Roles

Evaluate user-created roles within your SQL Server and determine if they can be replaced with built-in roles. Built-in roles, such as “db_datareader,” offer the advantage of fixed permissions that cannot be altered. Execute the following script to check for user-created roles:

--Server Principals
SELECT * 
FROM sys.server_principals
WHERE type = 'R' 
AND is_fixed_role = 0 
AND name NOT IN ('public')
GO

--Database Principals
EXEC sp_MSforeachdb 'USE ? 
SELECT db_name() as [Database name]
, name
, type
, create_date 
FROM sys.database_principals 
WHERE TYPE = ''R'' 
AND is_fixed_role = 0 
AND name NOT IN (''public'');'

Note: It is normal to have a few non-fixed roles in system databases and feature-related databases like Reporting Services.

Role Memberships

To identify the roles each user is associated with, execute the following scripts:

--Find Server-Level Role Memberships 
SELECT DP1.name AS ServerRoleName
,	isnull(DP2.name, 'No members') AS ServerUserName   
, 'EXEC sp_dropsrvrolemember ['+DP2.name+'], ['+DP1.name+'];' AS [Role Removal Script - Be EXTREMELY careful]
FROM sys.server_role_members AS DRM  
RIGHT OUTER JOIN sys.server_principals DP1  
	ON DRM.role_principal_id = DP1.principal_id  
LEFT OUTER JOIN sys.server_principals  DP2  
	ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.type = 'R' 
AND DP2.name NOT LIKE '%NT SER%'
AND DP2.name <>'sa'
ORDER BY DP1.name;

-- Find Database-Level Role Memberships 
EXEC sp_MSforeachdb 'USE ? 
SELECT ''?'' AS [Database name]
,	DP1.name AS DatabaseRoleName
,	isnull(DP2.name, ''No members'') AS ServerUserName   
, ''USE ''+ ''?'' + ''; EXEC sp_droprolemember [''+DP2.name+''], [''+DP1.name+''];  '' AS [Role Removal Script - Be EXTREMELY careful]
FROM sys.database_role_members AS DRM  
RIGHT OUTER JOIN sys.database_principals DP1  
	ON DRM.role_principal_id = DP1.principal_id  
LEFT OUTER JOIN sys.database_principals DP2  
	ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.type = ''R'' 
AND DP2.name NOT LIKE ''%##%''
AND DP2.name NOT LIKE ''%dbo%''
AND DP2.name NOT LIKE ''%dc_%''
AND DP2.name NOT LIKE ''%MS_%''
AND DP2.name NOT LIKE ''%Role%''
ORDER BY DP1.name; '

-- Edited on 8/31/2023 to edit correction script.

User-created and Public Role Permissions

There are a variety of ways to see what permissions a role has; however, this is my favorite since it puts it very nicely into a format that is easily read and understood, especially when comparing roles or looking to add or remove specific permissions. The following script, created by John Eisbrener, offers a convenient format for easy reading and comparison:

/********************************************************************
 *                                                                  *
 * Author: John Eisbrener                                           *
 * Script Purpose: Script out Database Role Definition              *
 * Notes: Please report any bugs to http://www.dbaeyes.com/         *
 *                                                                  *
 * Update: 2014-03-03 - Adjusted output to accommodate Role         *
 *                      definitions that are longer than 8000 chars *
 * Update: 2013-09-03 - Added user output per Joe Spivey's comment  *
 *                    - Modified formatting for oddly named objects *
 *                    - Included support for Grants on DMVs         *
 ********************************************************************/
DECLARE @roleName VARCHAR(255)
SET @roleName = 'DatabaseRoleName'

-- Script out the Role
DECLARE @roleDesc VARCHAR(MAX), @crlf VARCHAR(2)
SET @crlf = CHAR(13) + CHAR(10)
SET @roleDesc = 'CREATE ROLE [' + @roleName + ']' + @crlf + 'GO' + @crlf + @crlf

SELECT    @roleDesc = @roleDesc +
        CASE dp.state
            WHEN 'D' THEN 'DENY '
            WHEN 'G' THEN 'GRANT '
            WHEN 'R' THEN 'REVOKE '
            WHEN 'W' THEN 'GRANT '
        END + 
        dp.permission_name + ' ' +
        CASE dp.class
            WHEN 0 THEN ''
            WHEN 1 THEN --table or column subset on the table
                CASE WHEN dp.major_id < 0 THEN
                    + 'ON [sys].[' + OBJECT_NAME(dp.major_id) + '] '
                ELSE
                    + 'ON [' +
                    (SELECT SCHEMA_NAME(schema_id) + '].[' + name FROM sys.objects WHERE object_id = dp.major_id)
                        + -- optionally concatenate column names
                    CASE WHEN MAX(dp.minor_id) > 0 
                         THEN '] ([' + REPLACE(
                                        (SELECT name + '], [' 
                                         FROM sys.columns 
                                         WHERE object_id = dp.major_id 
                                            AND column_id IN (SELECT minor_id 
                                                              FROM sys.database_permissions 
                                                              WHERE major_id = dp.major_id
                                                                AND USER_NAME(grantee_principal_id) IN (@roleName)
                                                             )
                                         FOR XML PATH('')
                                        ) --replace final square bracket pair
                                    + '])', ', []', '')
                         ELSE ']'
                    END + ' '
                END
            WHEN 3 THEN 'ON SCHEMA::[' + SCHEMA_NAME(dp.major_id) + '] '
            WHEN 4 THEN 'ON ' + (SELECT RIGHT(type_desc, 4) + '::[' + name FROM sys.database_principals WHERE principal_id = dp.major_id) + '] '
            WHEN 5 THEN 'ON ASSEMBLY::[' + (SELECT name FROM sys.assemblies WHERE assembly_id = dp.major_id) + '] '
            WHEN 6 THEN 'ON TYPE::[' + (SELECT name FROM sys.types WHERE user_type_id = dp.major_id) + '] '
            WHEN 10 THEN 'ON XML SCHEMA COLLECTION::[' + (SELECT SCHEMA_NAME(schema_id) + '.' + name FROM sys.xml_schema_collections WHERE xml_collection_id = dp.major_id) + '] '
            WHEN 15 THEN 'ON MESSAGE TYPE::[' + (SELECT name FROM sys.service_message_types WHERE message_type_id = dp.major_id) + '] '
            WHEN 16 THEN 'ON CONTRACT::[' + (SELECT name FROM sys.service_contracts WHERE service_contract_id = dp.major_id) + '] '
            WHEN 17 THEN 'ON SERVICE::[' + (SELECT name FROM sys.services WHERE service_id = dp.major_id) + '] '
            WHEN 18 THEN 'ON REMOTE SERVICE BINDING::[' + (SELECT name FROM sys.remote_service_bindings WHERE remote_service_binding_id = dp.major_id) + '] '
            WHEN 19 THEN 'ON ROUTE::[' + (SELECT name FROM sys.routes WHERE route_id = dp.major_id) + '] '
            WHEN 23 THEN 'ON FULLTEXT CATALOG::[' + (SELECT name FROM sys.fulltext_catalogs WHERE fulltext_catalog_id = dp.major_id) + '] '
            WHEN 24 THEN 'ON SYMMETRIC KEY::[' + (SELECT name FROM sys.symmetric_keys WHERE symmetric_key_id = dp.major_id) + '] '
            WHEN 25 THEN 'ON CERTIFICATE::[' + (SELECT name FROM sys.certificates WHERE certificate_id = dp.major_id) + '] '
            WHEN 26 THEN 'ON ASYMMETRIC KEY::[' + (SELECT name FROM sys.asymmetric_keys WHERE asymmetric_key_id = dp.major_id) + '] '
         END COLLATE SQL_Latin1_General_CP1_CI_AS
         + 'TO [' + @roleName + ']' + 
         CASE dp.state WHEN 'W' THEN ' WITH GRANT OPTION' ELSE '' END + @crlf
FROM    sys.database_permissions dp
WHERE    USER_NAME(dp.grantee_principal_id) IN (@roleName)
GROUP BY dp.state, dp.major_id, dp.permission_name, dp.class

SELECT @roleDesc = @roleDesc + 'GO' + @crlf + @crlf      

-- Display users within Role.  Code stubbed by Joe Spivey
SELECT  @roleDesc = @roleDesc + 'EXECUTE sp_AddRoleMember ''' + roles.name + ''', ''' + users.name + '''' + @crlf
FROM    sys.database_principals users
        INNER JOIN sys.database_role_members link 
            ON link.member_principal_id = users.principal_id
        INNER JOIN sys.database_principals roles 
            ON roles.principal_id = link.role_principal_id
WHERE   roles.name = @roleName

-- PRINT out in blocks of up to 8000 based on last \r\n
DECLARE @printCur INT
SET @printCur = 8000

WHILE LEN(@roleDesc) > 8000
BEGIN
    -- Reverse first 8000 characters and look for first lf cr (reversed crlf) as delimiter
    SET @printCur = 8000 - CHARINDEX(CHAR(10) + CHAR(13), REVERSE(SUBSTRING(@roleDesc, 0, 8000)))

    PRINT LEFT(@roleDesc, @printCur)
    SELECT @roleDesc = RIGHT(@roleDesc, LEN(@roleDesc) - @printCur)
END

-- Output new permissions
PRINT @roleDesc + 'GO'

The output looks like this:

Check for Public Role Permissions

To ensure that no permissions have been added to the PUBLIC role, execute the following scripts:

-- Run this for Server-Level changes
SELECT permission_name AS [Permission],
       CASE
         WHEN perms.class = 101 THEN 'LOGIN'
         ELSE class_desc
       END             AS [Permission Class],
       CASE
         WHEN perms.class = 100 THEN @@SERVERNAME -- SERVER
         WHEN perms.class = 101 THEN prins.NAME -- SERVER_PRINCIPAL
         WHEN perms.class = 105 THEN ep.NAME -- ENDPOINT
       END             AS [Object]
FROM   sys.server_permissions AS perms
       LEFT JOIN sys.endpoints AS ep
              ON ep.endpoint_id = perms.major_id
       LEFT JOIN sys.server_principals AS prins
              ON prins.principal_id = perms.major_id
WHERE  grantee_principal_id = Suser_id('public')
       AND NOT (
                   (permission_name = 'CONNECT' AND perms.class = 105 AND ep.NAME = 'TSQL LOCAL MACHINE') OR
                   (permission_name = 'CONNECT' AND perms.class = 105 AND ep.NAME = 'TSQL Named Pipes') OR
                   (permission_name = 'CONNECT' AND perms.class = 105 AND ep.NAME = 'TSQL Default TCP') OR
                   (permission_name = 'CONNECT' AND perms.class = 105 AND ep.NAME = 'TSQL Default VIA') OR
                   (permission_name = 'VIEW ANY DATABASE' AND perms.class = 100)
               )


-- Run this for Database-Level changes
DECLARE @DB_Name VARCHAR(256),
	@SQL NVARCHAR(4000);

DECLARE public_cursor CURSOR FOR
	SELECT name 
	FROM MASTER.sys.databases
	WHERE database_id > 4 AND state = 0
	AND [name] not in (
		SELECT adc.database_name
		FROM sys.availability_replicas AS ar
       JOIN sys.availability_databases_cluster adc ON adc.group_id = ar.group_id
		WHERE ar.secondary_role_allow_connections = 0
       AND ar.replica_server_name = @@SERVERNAME
       AND sys.fn_hadr_is_primary_replica(adc.database_name) = 0 
		)

OPEN public_cursor 
FETCH NEXT FROM public_cursor INTO @DB_Name 

WHILE @@FETCH_STATUS = 0 
BEGIN 

SET @SQL = 'USE ' + QUOTENAME(@DB_Name) + '; ' +
'SELECT 
db_name() as [DatabaseName]
, ''The [public] role has been granted the permission ['' + perms.permission_name + ''] on the object [''
+ CASE
WHEN perms.class = 0 THEN db_name()
WHEN perms.class = 3 THEN schema_name(major_id)
WHEN perms.class = 4 THEN printarget.NAME
WHEN perms.class = 5 THEN asm.NAME
WHEN perms.class = 6 THEN type_name(major_id)
WHEN perms.class = 10 THEN xmlsc.NAME
WHEN perms.class = 15 THEN msgt.NAME COLLATE DATABASE_DEFAULT
WHEN perms.class = 16 THEN svcc.NAME COLLATE DATABASE_DEFAULT
WHEN perms.class = 17 THEN svcs.NAME COLLATE DATABASE_DEFAULT
WHEN perms.class = 18 THEN rsb.NAME COLLATE DATABASE_DEFAULT
WHEN perms.class = 19 THEN rts.NAME COLLATE DATABASE_DEFAULT
WHEN perms.class = 23 THEN ftc.NAME
WHEN perms.class = 24 THEN sym.NAME
WHEN perms.class = 25 THEN crt.NAME
WHEN perms.class = 26 THEN asym.NAME
END + ''].''
, ''Because these permissions are available to anyone who can connect to your instance, they should be revoked and granted to users, groups, or roles other than public.''
FROM sys.database_permissions AS perms
LEFT JOIN sys.database_principals AS prin ON perms.grantee_principal_id = prin.principal_id
LEFT JOIN sys.assemblies AS asm ON perms.major_id = asm.assembly_id
LEFT JOIN sys.xml_schema_collections AS xmlsc ON perms.major_id = xmlsc.xml_collection_id
LEFT JOIN sys.service_message_types AS msgt ON perms.major_id = msgt.message_type_id
LEFT JOIN sys.service_contracts AS svcc ON perms.major_id = svcc.service_contract_id
LEFT JOIN sys.services AS svcs ON perms.major_id = svcs.service_id
LEFT JOIN sys.remote_service_bindings AS rsb ON perms.major_id = rsb.remote_service_binding_id
LEFT JOIN sys.routes AS rts ON perms.major_id = rts.route_id
LEFT JOIN sys.database_principals AS printarget ON perms.major_id = printarget.principal_id
LEFT JOIN sys.symmetric_keys AS sym ON perms.major_id = sym.symmetric_key_id
LEFT JOIN sys.asymmetric_keys AS asym ON perms.major_id = asym.asymmetric_key_id
LEFT JOIN sys.certificates AS crt ON perms.major_id = crt.certificate_id
LEFT JOIN sys.fulltext_catalogs AS ftc ON perms.major_id = ftc.fulltext_catalog_id
WHERE perms.grantee_principal_id = DATABASE_PRINCIPAL_ID(''public'')
    AND class != 1 -- Object or Columns (class = 1) are handled by VA1054 and have different remediation syntax
    AND [state] IN (''G'',''W'')
    AND NOT (
        perms.class = 0
        AND prin.NAME = ''public''
        AND perms.major_id = 0
        AND perms.minor_id = 0
        AND permission_name IN (
            ''VIEW ANY COLUMN ENCRYPTION KEY DEFINITION''
            ,''VIEW ANY COLUMN MASTER KEY DEFINITION''
            )
        )'


	EXEC sp_executesql @SQL 

    /* iterate the cursor to the next database name */
    FETCH NEXT FROM public_cursor INTO @DB_Name 
END 
CLOSE public_cursor;
DEALLOCATE public_cursor;

It is recommended to use custom roles rather than modifying the Public role, which is provided by Microsoft. If any of the above scripts show additional permissions in the Public role, it should be reviewed and remediated immediately.

Domain Groups

In most cases, SQL environments involve domain groups, offering both advantages and potential risks. Domain groups simplify permission assignment, allowing users to be added or removed through Active Directory. However, it is crucial to verify the members of domain groups with access to SQL Server. Use the following command prompt script to determine the members of a domain group:

If you continue to use SQL Authenticated accounts, it is essential to ensure that the account passwords are not easily guessable. I adapted the script found here to check for commonly used passwords among the top 100,000 PWNED passwords available on the internet (– source: https://www.ncsc.gov.uk/static-assets/documents/PwnedPasswordsTop100k.txt). This script helps identify weak passwords and emphasizes the importance of enforcing password policies for SQL Authenticated accounts.

Security is of utmost importance, and it is vital to invest time in reviewing access, adjusting permissions, and safeguarding your data. This post and these scripts aim to facilitate the process of vulnerability assessment and access audit, making it more manageable and effective.

David Seis
Article by David Seis
David Seis is a Support Technician that joined Straight Path in the summer of 2021. His goal is to work in an area that he believes helps others, and to do it to the best of his ability. He loves to help, solve problems, and make things easy for others to understand. Because of this he is lovingly known as a “Good helper” among friends and family. Outside of work, he has a growing passion for cybersecurity, specifically helping protect those who are most vulnerable to fraud, scams, and phishing. He looks forward to learning more about each of his clients, helping minimize issues, and ensure that any problems are solved quickly.

Subscribe for Updates

Name

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share This