SQL Vulnerability Assessment Tool – Rules Reference List

Looking for an online reference page for the rules run by the SQL Vulnerability Assessment tool? Personally, I’ve been looking for such a page for a while now, but never found one. So eventually, I figured “why not do it myself?”. Cool script attached!

The script in the GitHub Gist below takes a JSON results file generated by a SQL Vulnerability Assessment Tool check, and parses it into a relational structure:

https://gist.github.com/EitanBlumin/ed3bc163fbcfa6f9e13a486b685517ce#file-parse-vulnerability-assessment-scan-json-file-sql

From there, it was a matter of some tinkering to turn it into an HTML page, and create an online reference page for you guys.

Anyways, table of contents is available below. Click on the description of each rule for more details, including the relevant T-SQL query and corresponding remediation details. Fingers crossed, the search engines won’t kill me 🤞.

Enjoy!

Disclaimer: The information below is copyrighted to the Microsoft Corporation. I do not hold any rights to it, and it is publicly available to everyone downloading the latest version of SQL Server Management Studio and running a Vulnerability Assessment on any SQL Server database.

Auditing And Logging – 3 rule(s)

Severity: Medium

VA1265: Auditing of both successful and failed login attempts for contained DB authentication should be enabled

SQL Server auditing configuration enables adminstrators to track users logging to SQL Server instances that they’re responsible for. This rules checks that auditing is enabled for both successful and failed login attempts for contained DB authentication.

Rationale:

Logging successful & failed login attempts provides information that can be used to detect brute-force based password attacks against the system as well as forensic information.

Query:

DECLARE @check_results INT = 0; 
DECLARE @violation INT = 1; 

SELECT @check_results = containment 
FROM   sys.databases 
WHERE  database_id = Db_id(); 

PRINT @check_results 

IF( @check_results != 0 ) 
  BEGIN 
      DECLARE @success_logon_event INT = 0; 
      DECLARE @fail_logon_event INT = 0; 

      SELECT @success_logon_event = Count(*) 
      FROM   sys.server_audits adts, 
             sys.server_audit_specifications srvadtspecs, 
             sys.server_audit_specification_details srvadtspecdtls 
      WHERE  adts.audit_guid = srvadtspecs.audit_guid 
             AND adts.is_state_enabled = 1 
             AND srvadtspecs.is_state_enabled = 1 
             AND srvadtspecdtls.audited_result = 'SUCCESS AND FAILURE' 
             AND srvadtspecdtls.audit_action_id = 'DAGS'; 

      SELECT @fail_logon_event = Count(*) 
      FROM   sys.server_audits adts, 
             sys.server_audit_specifications srvadtspecs, 
             sys.server_audit_specification_details srvadtspecdtls 
      WHERE  adts.audit_guid = srvadtspecs.audit_guid 
             AND adts.is_state_enabled = 1 
             AND srvadtspecs.is_state_enabled = 1 
             AND srvadtspecdtls.audited_result = 'SUCCESS AND FAILURE' 
             AND srvadtspecdtls.audit_action_id = 'DAGF'; 

      DECLARE @db_success_logon_event INT = 0; 
      DECLARE @db_fail_logon_event INT = 0; 

      SELECT @db_success_logon_event = Count(*) 
      FROM   sys.server_audits adts, 
             sys.database_audit_specifications dbadtspecs, 
             sys.database_audit_specification_details dbadtspecdtls 
      WHERE  adts.audit_guid = dbadtspecs.audit_guid 
             AND adts.is_state_enabled = 1 
             AND dbadtspecs.is_state_enabled = 1 
             AND dbadtspecdtls.audited_result = 'SUCCESS AND FAILURE' 
             AND dbadtspecdtls.audit_action_id = 'DAGS'; 

      SELECT @db_fail_logon_event = Count(*) 
      FROM   sys.server_audits adts, 
             sys.database_audit_specifications dbadtspecs, 
             sys.database_audit_specification_details dbadtspecdtls 
      WHERE  adts.audit_guid = dbadtspecs.audit_guid 
             AND adts.is_state_enabled = 1 
             AND dbadtspecs.is_state_enabled = 1 
             AND dbadtspecdtls.audited_result = 'SUCCESS AND FAILURE' 
             AND dbadtspecdtls.audit_action_id = 'DAGF'; 

      IF( ( @success_logon_event 
            + @db_success_logon_event ) > 0 
          AND ( @fail_logon_event + @db_fail_logon_event ) > 0 ) 
        SET @violation = 0; 
  END 
ELSE 
  BEGIN 
      SET @violation = 0; -- ignore if DB is not contained 
  END 

SELECT @violation AS [Violation];

Remediation:

Create and enable a new SERVER AUDIT SPECIFICATION that will audit FAILED_LOGIN_GROUP & SUCCESSFUL_LOGIN_GROUP events. This SERVER AUDIT SPECIFICATION must target a valid SERVER AUDIT object that is enabled.

CREATE DATABASE AUDIT SPECIFICATION [DbAuditSpec_db_logon_information_failed_succeessful]
    FOR SERVER AUDIT []
ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),
ADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP)
WITH (STATE = ON)

VA1281: All memberships for user-defined roles should be intended

User-defined roles are security principals defined by the user to group principals to easily manage permissions. Monitoring these roles is important to avoid having excessive permissions. Create a baseline which defines expected membership for each user-defined role. This rule checks whether all memberships for user-defined roles are as defined in the baseline

Rationale:

Keeping track of role memberships is important to avoid granting excessive permissions

Query:

SELECT user_name(role_principal_id) as role_name, user_name(member_principal_id) as member_name 
FROM sys.database_role_members 
WHERE role_principal_id NOT IN (16384,16385,16386,16387,16389,16390,16391,16392,16393) 
ORDER BY role_principal_id, member_principal_id

Remediation:

Keep track of role membership and remove unnecessary members from roles to avoid granting excessive permissions or update baseline to comply with new changes

ALTER ROLE [$0] DROP MEMBER [$1]

☝ back to top


Severity: Low

VA1253: List of DB-scoped events being audited and centrally managed via server audit specifications.

Auditing an instance of the SQL Server Database Engine or an individual database involves tracking and logging events that occur on the Database Engine. This rule displays a comprehensive list of all events currently being audited (i.e. linked to an audit that is enabled) that are DB-specific managed.

Rationale:

All of the events listed will be audited at a DB scope. Please notice that some events may be audited at server scoped (centrally managed).

Query:

SELECT dbadtspecs.NAME AS [Name],
    dbadtspecdtls.audit_action_name AS [Audit Action Name],
    audited_result AS [Audit Result] 
FROM   sys.server_audits adts, 
       sys.database_audit_specifications dbadtspecs, 
       sys.database_audit_specification_details dbadtspecdtls 
WHERE  adts.audit_guid = dbadtspecs.audit_guid 
       AND adts.is_state_enabled = 1 
       AND dbadtspecs.is_state_enabled = 1 
       AND dbadtspecdtls.audited_result = 'SUCCESS AND FAILURE' 
ORDER  BY dbadtspecdtls.audit_action_name, 
          class_desc, 
          major_id, 
          minor_id, 
          audited_principal_id

Remediation:

Review the list of events and make sure they match your needs. For more details visit https://msdn.microsoft.com/en-us/library/cc280386.aspx.

☝ back to top


Authentication And Authorization – 36 rule(s)

Severity: High

VA1020: Server principal GUEST should not be a member of any role

The guest user permits access to a database for any logins that are not mapped to a specific database user. This rule checks that no database roles are assigned to the Guest user.

Rationale:

Database Roles are the basic building block at the heart of separation of duties and the principle of least permission. Granting the Guest user membership to specific roles defeats this purpose.

Query:

SELECT name as [Role]
    FROM sys.database_role_members AS drms 
    JOIN sys.database_principals AS dps 
    ON drms.role_principal_id = dps.principal_id 
    WHERE member_principal_id = DATABASE_PRINCIPAL_ID('guest')

Remediation:

Remove the special principal GUEST from all roles.

ALTER ROLE [$0] DROP MEMBER GUEST

VA2000: Minimal set of principals should be granted high impact database-scoped permissions

Every SQL Server securable has permissions associated with it that can be granted to principals. Permissions can be scoped at the server level (assigned to logins and server roles) or at the database level (assigned to database users and database roles). These rules check that only a minimal set of principals are granted high impact database-scoped permissions.

Rationale:

Developing an application using a least-privileged user account (LUA) approach is an important part of a defensive, in-depth strategy for countering security threats. The LUA approach ensures that users follow the principle of least privilege and always log on with limited user accounts. Administrative tasks are broken out using fixed server roles, and the use of the sysadmin fixed server role is severely restricted. Always follow the principle of least privilege when granting permissions to database users. Grant the minimum permissions necessary to a user or role to accomplish a given task. See https://msdn.microsoft.com/en-us/library/bb669084(v=vs.110).aspx.

Query:

SELECT perms.class_desc AS [Permission Class]
    ,perms.permission_name AS Permission
    ,type_desc AS [Principal Type]
    ,prin.name AS Principal
FROM sys.database_permissions perms
    ,sys.database_principals prin
WHERE perms.grantee_principal_id = prin.principal_id
    AND permission_name IN (
        'CONTROL'
        ,'AUTHENTICATE'
        ,'TAKE OWNERSHIP'
        ,'ALTER ANY ASSEMBLY'
        ,'ALTER ANY DATABASE DDL TRIGGER'
        ,'CREATE DATABASE DDL EVENT NOTIFICATION'
        ,'KILL DATABASE CONNECTION'
        ,'CREATE DATABASE'
        ,'BACKUP DATABASE'
        ,'BACKUP LOG'
        ,'CREATE REMOTE SERVICE BINDING'
        ,'CREATE ROUTE'
        ,'CREATE FULLTEXT CATALOG'
        ,'CREATE ASSEMBLY'
        ,'REFERENCES'
        )
    AND grantee_principal_id NOT IN (
        DATABASE_PRINCIPAL_ID('guest')
        ,DATABASE_PRINCIPAL_ID('public')
        )
    AND perms.class = 0
    AND [state] IN ('G','W')

Remediation:

Revoke permissions from principals where not needed. It is recommended to have at most 1 principal granted a specific permission.

REVOKE $1 FROM [$3]

VA2001: Minimal set of principals should be granted high impact database-scoped permissions on objects or columns

Every SQL Server securable has permissions associated with it that can be granted to principals. Permissions can be scoped at the server level (assigned to logins and server roles) or at the database level (assigned to database users and database roles). These rules check that only a minimal set of principals are granted high impact database-scoped permissions on objects or columns.

Rationale:

Developing an application using a least-privileged user account (LUA) approach is an important part of a defensive, in-depth strategy for countering security threats. The LUA approach ensures that users follow the principle of least privilege and always log on with limited user accounts. Administrative tasks are broken out using fixed server roles, and the use of the sysadmin fixed server role is severely restricted. Always follow the principle of least privilege when granting permissions to database users. Grant the minimum permissions necessary to a user or role to accomplish a given task. See https://msdn.microsoft.com/en-us/library/bb669084(v=vs.110).aspx.

Query:

SELECT perms.class_desc AS [Permission Class]
    ,object_schema_name(major_id) AS [Schema Name]
    ,object_name(major_id) AS [Object]
    ,perms.permission_name AS Permission
    ,type_desc AS [Principal Type]
    ,prin.name AS Principal
FROM sys.database_permissions perms
    ,sys.database_principals prin
WHERE perms.grantee_principal_id = prin.principal_id
    AND permission_name IN (
        'CONTROL'
        ,'TAKE OWNERSHIP'
        ,'REFERENCES'
        )
    AND grantee_principal_id NOT IN (
        DATABASE_PRINCIPAL_ID('guest')
        ,DATABASE_PRINCIPAL_ID('public')
        )
    AND perms.class = 1
    AND [state] IN ('G','W')

Remediation:

Revoke permissions from principals where not needed. It is recommended to have at most 1 principal granted a specific permission.

REVOKE $3 ON [$1].[$2] FROM [$5]

VA2002: Minimal set of principals should be granted high impact database-scoped permissions on various securables

Every SQL Server securable has permissions associated with it that can be granted to principals. Permissions can be scoped at the server level (assigned to logins and server roles) or at the database level (assigned to database users and database roles). These rules check that only a minimal set of principals are granted high impact database-scoped permissions on various securables.

Rationale:

Developing an application using a least-privileged user account (LUA) approach is an important part of a defensive, in-depth strategy for countering security threats. The LUA approach ensures that users follow the principle of least privilege and always log on with limited user accounts. Administrative tasks are broken out using fixed server roles, and the use of the sysadmin fixed server role is severely restricted. Always follow the principle of least privilege when granting permissions to database users. Grant the minimum permissions necessary to a user or role to accomplish a given task. See https://msdn.microsoft.com/en-us/library/bb669084(v=vs.110).aspx.

Query:

SELECT REPLACE(perms.class_desc, '_', ' ') AS [Permission Class], 
    CASE 
        WHEN perms.class=3 THEN schema_name(major_id) -- schema
        WHEN perms.class=4 THEN printarget.name -- principal
        WHEN perms.class=5 THEN asm.name -- assembly
        WHEN perms.class=6 THEN type_name(major_id) -- type
        WHEN perms.class=10 THEN xmlsc.name -- xml schema
        WHEN perms.class=15 THEN msgt.name COLLATE DATABASE_DEFAULT -- message types
        WHEN perms.class=16 THEN svcc.name COLLATE DATABASE_DEFAULT -- service contracts
        WHEN perms.class=17 THEN svcs.name COLLATE DATABASE_DEFAULT -- services
        WHEN perms.class=18 THEN rsb.name COLLATE DATABASE_DEFAULT -- remote service bindings
        WHEN perms.class=19 THEN rts.name COLLATE DATABASE_DEFAULT -- routes
        WHEN perms.class=23 THEN ftc.name -- full text catalog
        WHEN perms.class=24 then sym.name -- symmetric key
        WHEN perms.class=25 then crt.name -- certificate
        WHEN perms.class=26 then asym.name -- assymetric key
    END AS [Object],
    perms.permission_name AS Permission, 
    prin.type_desc AS [Principal Type], 
    prin.name AS Principal
FROM sys.database_permissions perms
LEFT JOIN
    sys.database_principals prin
    ON perms.grantee_principal_id = prin.principal_id
LEFT JOIN 
    sys.assemblies asm
    ON perms.major_id = asm.assembly_id
LEFT JOIN 
    sys.xml_schema_collections xmlsc
    ON perms.major_id = xmlsc.xml_collection_id
LEFT JOIN 
    sys.service_message_types msgt
    ON perms.major_id = msgt.message_type_id
LEFT JOIN 
    sys.service_contracts svcc
    ON perms.major_id = svcc.service_contract_id
LEFT JOIN 
    sys.services svcs
    ON perms.major_id = svcs.service_id
LEFT JOIN 
    sys.remote_service_bindings rsb
    ON perms.major_id = rsb.remote_service_binding_id
LEFT JOIN 
    sys.routes rts
    ON perms.major_id = rts.route_id
LEFT JOIN
    sys.database_principals printarget
    ON perms.major_id = printarget.principal_id
LEFT JOIN    
    sys.symmetric_keys sym
    On perms.major_id = sym.symmetric_key_id
LEFT JOIN
    sys.asymmetric_keys asym
    ON perms.major_id = asym.asymmetric_key_id
    LEFT JOIN
    sys.certificates crt
    ON perms.major_id = crt.certificate_id
LEFT JOIN
    sys.fulltext_catalogs ftc
    ON perms.major_id = ftc.fulltext_catalog_id
WHERE  
    permission_name IN ('CONTROL', 'TAKE OWNERSHIP', 'REFERENCES') 
    AND grantee_principal_id NOT IN (DATABASE_PRINCIPAL_ID('guest'), DATABASE_PRINCIPAL_ID('public')) 
    AND class in (3,4,5,6,10,15,16,17,18,19,23,24,25,26)
    AND [state] IN ('G','W')

Remediation:

Revoke permissions from principals where not needed. It is recommended to have at most 1 principal granted a specific permission.

REVOKE $2 ON $0::[$1] FROM [$4]

VA2020: Minimal set of principals should be granted ALTER or ALTER ANY USER database-scoped permissions

Every SQL Server securable has permissions associated with it that can be granted to principals. Permissions can be scoped at the server level (assigned to logins and server roles) or at the database level (assigned to database users and database roles). These rules check that only a minimal set of principals are granted ALTER or ALTER ANY USER database-scoped permissions.

Rationale:

Developing an application using a least-privileged user account (LUA) approach is an important part of a defensive, in-depth strategy for countering security threats. The LUA approach ensures that users follow the principle of least privilege and always log on with limited user accounts. Administrative tasks are broken out using fixed server roles, and the use of the sysadmin fixed server role is severely restricted. Always follow the principle of least privilege when granting permissions to database users. Grant the minimum permissions necessary to a user or role to accomplish a given task. See https://msdn.microsoft.com/en-us/library/bb669084(v=vs.110).aspx.

Query:

SELECT perms.class_desc AS [Permission Class]
    ,perms.permission_name AS Permission
    ,type_desc AS [Principal Type]
    ,prin.name AS Principal
FROM sys.database_permissions perms
    ,sys.database_principals prin
WHERE perms.grantee_principal_id = prin.principal_id
    AND permission_name IN (
        'ALTER'
        ,'ALTER ANY USER'
        )
    AND user_name(grantee_principal_id) NOT IN (
        'guest'
        ,'public'
        )
    AND perms.class = 0
    AND [state] IN ('G','W')
    AND NOT (
        prin.type = 'S'
        AND prin.name = 'dbo'
        AND prin.authentication_type = 1
        AND prin.owning_principal_id IS NULL
        )

Remediation:

Revoke permissions from principals where not needed. It is recommended to have at most 1 principal granted a specific permission.

REVOKE $1 FROM [$3]

VA2021: Minimal set of principals should be granted database-scoped ALTER permissions on objects or columns

Every SQL Server securable has permissions associated with it that can be granted to principals. Permissions can be scoped at the server level (assigned to logins and server roles) or at the database level (assigned to database users and database roles). These rules check that only a minimal set of principals are granted ALTER database-scoped permissions on objects or colums.

Rationale:

Developing an application using a least-privileged user account (LUA) approach is an important part of a defensive, in-depth strategy for countering security threats. The LUA approach ensures that users follow the principle of least privilege and always log on with limited user accounts. Administrative tasks are broken out using fixed server roles, and the use of the sysadmin fixed server role is severely restricted. Always follow the principle of least privilege when granting permissions to database users. Grant the minimum permissions necessary to a user or role to accomplish a given task. See https://msdn.microsoft.com/en-us/library/bb669084(v=vs.110).aspx.

Query:

SELECT perms.class_desc AS [Permission Class]
    ,object_schema_name(major_id) AS [Schema Name]
    ,object_name(major_id) AS [Object]
    ,perms.permission_name AS Permission
    ,type_desc AS [Principal Type]
    ,prin.name AS Principal
FROM sys.database_permissions perms
    ,sys.database_principals prin
WHERE perms.grantee_principal_id = prin.principal_id
    AND permission_name = 'ALTER'
    AND grantee_principal_id NOT IN (
        DATABASE_PRINCIPAL_ID('guest')
        ,DATABASE_PRINCIPAL_ID('public')
        )
    AND perms.class = 1
    AND [state] IN ('G','W')
    AND NOT (
        prin.type = 'S'
        AND prin.name = 'dbo'
        AND prin.authentication_type = 1
        AND prin.owning_principal_id IS NULL
        )

Remediation:

Revoke permissions from principals where not needed. It is recommended to have at most 1 principal granted a specific permission.

REVOKE $3 ON [$1].[$2] FROM [$5]

VA2022: Minimal set of principals should be granted database-scoped ALTER permission on various securables

Every SQL Server securable has permissions associated with it that can be granted to principals. Permissions can be scoped at the server level (assigned to logins and server roles) or at the database level (assigned to database users and database roles). These rules check that only a minimal set of principals are granted database-scoped ALTER permissions on various securables.

Rationale:

Developing an application using a least-privileged user account (LUA) approach is an important part of a defensive, in-depth strategy for countering security threats. The LUA approach ensures that users follow the principle of least privilege and always log on with limited user accounts. Administrative tasks are broken out using fixed server roles, and the use of the sysadmin fixed server role is severely restricted. Always follow the principle of least privilege when granting permissions to database users. Grant the minimum permissions necessary to a user or role to accomplish a given task. See https://msdn.microsoft.com/en-us/library/bb669084(v=vs.110).aspx.

Query:

SELECT REPLACE(REPLACE(perms.class_desc, 'DATABASE_PRINCIPAL', 'ROLE'), '_', ' ') AS [Permission Class]
    ,CASE 
        WHEN perms.class = 3
            THEN schema_name(major_id) -- schema
        WHEN perms.class = 4
            THEN printarget.name -- principal
        WHEN perms.class = 5
            THEN asm.name -- assembly
        WHEN perms.class = 6
            THEN type_name(major_id) -- type
        WHEN perms.class = 10
            THEN xmlsc.name -- xml schema
        WHEN perms.class = 15
            THEN msgt.name COLLATE DATABASE_DEFAULT -- message types
        WHEN perms.class = 16
            THEN svcc.name COLLATE DATABASE_DEFAULT -- service contracts
        WHEN perms.class = 17
            THEN svcs.name COLLATE DATABASE_DEFAULT -- services
        WHEN perms.class = 18
            THEN rsb.name COLLATE DATABASE_DEFAULT -- remote service bindings
        WHEN perms.class = 19
            THEN rts.name COLLATE DATABASE_DEFAULT -- routes
        WHEN perms.class = 23
            THEN ftc.name -- full text catalog
        WHEN perms.class = 24
            THEN sym.name -- symmetric key
        WHEN perms.class = 25
            THEN crt.name -- certificate
        WHEN perms.class = 26
            THEN asym.name -- asymmetric key
        ELSE ''
        END AS [Object]
    ,perms.permission_name AS [Permission]
    ,prin.type_desc AS [Principal Type]
    ,prin.name AS [Principal]
FROM sys.database_permissions perms
LEFT JOIN sys.database_principals prin ON perms.grantee_principal_id = prin.principal_id
LEFT JOIN sys.assemblies asm ON perms.major_id = asm.assembly_id
LEFT JOIN sys.xml_schema_collections xmlsc ON perms.major_id = xmlsc.xml_collection_id
LEFT JOIN sys.service_message_types msgt ON perms.major_id = msgt.message_type_id
LEFT JOIN sys.service_contracts svcc ON perms.major_id = svcc.service_contract_id
LEFT JOIN sys.services svcs ON perms.major_id = svcs.service_id
LEFT JOIN sys.remote_service_bindings rsb ON perms.major_id = rsb.remote_service_binding_id
LEFT JOIN sys.routes rts ON perms.major_id = rts.route_id
LEFT JOIN sys.database_principals printarget ON perms.major_id = printarget.principal_id
LEFT JOIN sys.symmetric_keys sym ON perms.major_id = sym.symmetric_key_id
LEFT JOIN sys.asymmetric_keys asym ON perms.major_id = asym.asymmetric_key_id
LEFT JOIN sys.certificates crt ON perms.major_id = crt.certificate_id
LEFT JOIN sys.fulltext_catalogs ftc ON perms.major_id = ftc.fulltext_catalog_id
WHERE permission_name = 'ALTER'
    AND class IN (
        3
        ,4
        ,5
        ,6
        ,10
        ,15
        ,16
        ,17
        ,18
        ,19
        ,23
        ,24
        ,25
        ,26
        )
    AND user_name(grantee_principal_id) NOT IN (
        'guest'
        ,'public'
        )
    AND [state] IN ('G','W')
    AND NOT (
        prin.type = 'S'
        AND prin.name = 'dbo'
        AND prin.authentication_type = 1
        AND prin.owning_principal_id IS NULL
        )

Remediation:

Revoke permissions from principals where not needed. It is recommended to have at most 1 principal granted a specific permission.

REVOKE $2 ON $0::[$1] FROM [$4]

VA2108: Minimal set of principals should be members of fixed high impact database roles

SQL Server provides roles to help manage the permissions. Roles are security principals that group other principals. Database-level roles are database-wide in their permission scope. This rule checks that a minimal set of principals are members of the fixed database roles.

Rationale:

Fixed database roles may have administrative permissions on the system. Following the principle of least privilege, it is important to minimize membership in fixed database roles and keep a baseline of these memberships. See https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles for additional information on database roles.

Query:

SELECT user_name(sr.member_principal_id) as [Principal], user_name(sr.role_principal_id) as [Role], type_desc as [Principal Type], authentication_type_desc as [Authentication Type]
FROM sys.database_role_members sr, sys.database_principals sp   
WHERE sp.principal_id = sr.member_principal_id 
AND sr.role_principal_id IN (user_id('bulkadmin'), 
                             user_id('db_accessadmin'),
                             user_id('db_securityadmin'),
                             user_id('db_ddladmin'),
                             user_id('db_backupoperator'))
ORDER BY sp.name

Remediation:

Remove members who should not have access to the database role

ALTER ROLE [$1] DROP MEMBER [$0]

☝ back to top


Severity: Medium

VA1042: Database ownership chaining should be disabled for all databases except for ‘master’ and ‘tempdb’

Cross database ownership chaining is an extension of ownership chaining, except it does cross the database boundary. This rule checks that this option is disabled for all databases except for ‘master’ and ‘tempdb’.

Rationale:

This option enables privileged users on a database to perform DML operations on a different database that has also been enabled, essentially bypassing permissions on the target DB.

Query:

SELECT CASE 
         WHEN EXISTS (SELECT * 
                      FROM   sys.databases 
                      WHERE  NAME = Db_name() 
                             AND is_db_chaining_on = 1) THEN 1 
         ELSE 0 
       END       AS Violation, 
       Db_name() AS [Database]

Remediation:

Disable DB_CHANING option for this database

ALTER DATABASE [$1] SET DB_CHAINING OFF

VA1043: Principal GUEST should not have access to any user database

The guest user permits access to a database for any logins that are not mapped to a specific database user. This rule checks that the guest user cannot connect to any database.

Rationale:

The special user GUEST is used to map any login that otherwise has no access to the database. This can result in principals gaining access to a database without having been explicitly granted permission to do so.

Query:

SELECT CASE 
        WHEN EXISTS (
                SELECT *
                FROM sys.database_permissions perms
                JOIN sys.database_principals usrs ON grantee_principal_id = principal_id
                WHERE grantee_principal_id = Database_principal_id('guest')
                    AND perms.type = 'CO'
                    AND [state] IN (
        'G'
        ,'W'
                        )
                )
            THEN 1
        ELSE 0
        END AS Violation

Remediation:

Remove all permissions granted to GUEST, especially the connect permission. The easiest way to perform such action is by using DROP USER [guest] syntax

DROP USER [guest]

VA1095: Excessive permissions should not be granted to PUBLIC role

Every SQL Server login belongs to the public server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. This displays a list of all permissions that are granted to the PUBLIC role.

Rationale:

Database Roles are the basic building block at the heart of separation of duties and the principle of least permission. Granting permissions to principals through the default PUBLIC role defeats this purpose.

Query:

SELECT REPLACE(perms.class_desc, '_', ' ') AS [Permission Class]
    ,CASE 
        WHEN perms.class = 0
            THEN db_name() -- database
        WHEN perms.class = 3
            THEN schema_name(major_id) -- schema
        WHEN perms.class = 4
            THEN printarget.NAME -- principal
        WHEN perms.class = 5
            THEN asm.NAME -- assembly
        WHEN perms.class = 6
            THEN type_name(major_id) -- type
        WHEN perms.class = 10
            THEN xmlsc.NAME -- xml schema
        WHEN perms.class = 15
            THEN msgt.NAME COLLATE DATABASE_DEFAULT -- message types
        WHEN perms.class = 16
            THEN svcc.NAME COLLATE DATABASE_DEFAULT -- service contracts
        WHEN perms.class = 17
            THEN svcs.NAME COLLATE DATABASE_DEFAULT -- services
        WHEN perms.class = 18
            THEN rsb.NAME COLLATE DATABASE_DEFAULT -- remote service bindings
        WHEN perms.class = 19
            THEN rts.NAME COLLATE DATABASE_DEFAULT -- routes
        WHEN perms.class = 23
            THEN ftc.NAME -- full text catalog
        WHEN perms.class = 24
            THEN sym.NAME -- symmetric key
        WHEN perms.class = 25
            THEN crt.NAME -- certificate
        WHEN perms.class = 26
            THEN asym.NAME -- assymetric key
        END AS [Object]
    ,perms.permission_name AS Permission
FROM sys.database_permissions AS perms
LEFT JOIN sys.database_principals prin ON perms.grantee_principal_id = prin.principal_id
LEFT JOIN sys.assemblies asm ON perms.major_id = asm.assembly_id
LEFT JOIN sys.xml_schema_collections xmlsc ON perms.major_id = xmlsc.xml_collection_id
LEFT JOIN sys.service_message_types msgt ON perms.major_id = msgt.message_type_id
LEFT JOIN sys.service_contracts svcc ON perms.major_id = svcc.service_contract_id
LEFT JOIN sys.services svcs ON perms.major_id = svcs.service_id
LEFT JOIN sys.remote_service_bindings rsb ON perms.major_id = rsb.remote_service_binding_id
LEFT JOIN sys.routes rts ON perms.major_id = rts.route_id
LEFT JOIN sys.database_principals printarget ON perms.major_id = printarget.principal_id
LEFT JOIN sys.symmetric_keys sym ON perms.major_id = sym.symmetric_key_id
LEFT JOIN sys.asymmetric_keys asym ON perms.major_id = asym.asymmetric_key_id
LEFT JOIN sys.certificates crt ON perms.major_id = crt.certificate_id
LEFT JOIN sys.fulltext_catalogs 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'
            )
        )
ORDER BY perms.class
    ,object_name(perms.major_id)
    ,perms.grantor_principal_id
    ,perms.STATE

Remediation:

Revoke any unnecessary permissions granted to PUBLIC, but avoid changing permissions granted out of the box.

REVOKE $2 ON $0::[$1] FROM PUBLIC

VA1248: User-defined database roles should not be members of fixed roles

To easily manage the permissions in your databases, SQL Server provides several roles which are security principals that group other principals. They are like groups in the Microsoft Windows operating system. Database accounts and other SQL Server roles can be added into database-level roles. Each member of a fixed-database role can add other users to that same role. This rule checks that no user-defined roles are members of fixed roles

Rationale:

Adding user defined database roles as members of fixed roles could enable unintended privilege escalation, also finding any metadata indicating that the fixed roles have been modified is typically a sign of data corruption or signs of somebody corrupting the metadata in order to hide unusual activity.

Query:

SELECT user_name(roles.role_principal_id) as role, user_name(roles.member_principal_id) as member 
FROM sys.database_role_members roles, sys.database_principals users 
WHERE roles.member_principal_id = users.principal_id 
AND ( roles.role_principal_id >= 16384 AND roles.role_principal_id <= 16393) 
AND users.type = 'R'  
ORDER BY user_name(roles.role_principal_id), user_name(roles.member_principal_id)

Remediation:

Remove user defined roles from fixed-database roles

ALTER ROLE [$0] DROP MEMBER [$1]

VA1267: Contained users should use Windows Authentication

Contained users are users that exist within the database, and do not require a login mapping. This rule checks that contained users use Windows Authentication.

Rationale:

Authentication must be centrally managed in order to enforce the domain password policies.

Query:

SELECT NAME AS [Principal]
FROM   sys.database_principals 
WHERE  authentication_type = 2 
ORDER  BY NAME, 
          type_desc, 
          authentication_type

Remediation:

Remove all contained users with password. All affected applications will have to switch to Windows authentication, or create logins with password policy enforcement in order to connect to the DB.

DROP USER [$0];

VA2010: Minimal set of principals should be granted medium impact database-scoped permissions

Every SQL Server securable has permissions associated with it that can be granted to principals. Permissions can be scoped at the server level (assigned to logins and server roles) or at the database level (assigned to database users and database roles). These rules check that only a minimal set of principals are granted medium impact database-scoped permissions.

Rationale:

Developing an application using a least-privileged user account (LUA) approach is an important part of a defensive, in-depth strategy for countering security threats. The LUA approach ensures that users follow the principle of least privilege and always log on with limited user accounts. Administrative tasks are broken out using fixed server roles, and the use of the sysadmin fixed server role is severely restricted. Always follow the principle of least privilege when granting permissions to database users. Grant the minimum permissions necessary to a user or role to accomplish a given task. See https://msdn.microsoft.com/en-us/library/bb669084(v=vs.110).aspx.

Query:

SELECT perms.class_desc AS [Permission Class]
    ,perms.permission_name AS Permission
    ,type_desc AS [Principal Type]
    ,prin.name AS Principal
FROM sys.database_permissions perms
    ,sys.database_principals prin
WHERE perms.grantee_principal_id = prin.principal_id
    AND permission_name IN (
        'ALTER ANY ROLE'
        ,'ALTER ANY APPLICATION ROLE'
        ,'ALTER ANY SCHEMA'
        ,'ALTER ANY DATASPACE'
        ,'ALTER ANY MESSAGE TYPE'
        ,'ALTER ANY CONTRACT'
        ,'ALTER ANY SERVICE'
        ,'ALTER ANY REMOTE SERVICE BINDING'
        ,'ALTER ANY ROUTE'
        ,'ALTER ANY FULLTEXT CATALOG'
        ,'ALTER ANY SYMMETRIC KEY'
        ,'ALTER ANY ASYMMETRIC KEY'
        ,'ALTER ANY CERTIFICATE'
        ,'ALTER ANY DATABASE EVENT NOTIFICATION'
        ,'ALTER ANY DATABASE AUDIT'
        ,'ALTER ANY DATABASE EVENT SESSION'
        ,'SHOWPLAN'
        ,'CONNECT REPLICATION'
        ,'CHECKPOINT'
        ,'SUBSCRIBE QUERY NOTIFICATIONS'
        ,'VIEW DATABASE STATE'
        ,'CREATE TABLE'
        ,'CREATE VIEW'
        ,'CREATE PROCEDURE'
        ,'CREATE FUNCTION'
        ,'CREATE RULE'
        ,'CREATE DEFAULT'
        ,'CREATE TYPE'
        ,'CREATE XML SCHEMA COLLECTION'
        ,'CREATE SCHEMA'
        ,'CREATE SYNONYM'
        ,'CREATE AGGREGATE'
        ,'CREATE ROLE'
        ,'CREATE MESSAGE TYPE'
        ,'CREATE SERVICE'
        ,'CREATE CONTRACT'
        ,'CREATE QUEUE'
        ,'CREATE SYMMETRIC KEY'
        ,'CREATE ASYMMETRIC KEY'
        ,'CREATE CERTIFICATE'
        )
    AND grantee_principal_id NOT IN (
        DATABASE_PRINCIPAL_ID('guest')
        ,DATABASE_PRINCIPAL_ID('public')
        )
    AND perms.class = 0
    AND [state] IN ('G','W')

Remediation:

Revoke permissions from principals where not needed. It is recommended to have at most 1 principal granted a specific permission.

REVOKE $1 FROM [$3]

VA2050: Minimal set of principals should be granted database-scoped VIEW DEFINITION permissions

Every SQL Server securable has permissions associated with it that can be granted to principals. Permissions can be scoped at the server level (assigned to logins and server roles) or at the database level (assigned to database users and database roles). These rules check that only a minimal set of principals are granted database-scoped VIEW DEFINITION permissions.

Rationale:

Developing an application using a least-privileged user account (LUA) approach is an important part of a defensive, in-depth strategy for countering security threats. The LUA approach ensures that users follow the principle of least privilege and always log on with limited user accounts. Administrative tasks are broken out using fixed server roles, and the use of the sysadmin fixed server role is severely restricted. Always follow the principle of least privilege when granting permissions to database users. Grant the minimum permissions necessary to a user or role to accomplish a given task. See https://msdn.microsoft.com/en-us/library/bb669084(v=vs.110).aspx.

Query:

SELECT prin.NAME AS Principal
FROM sys.database_permissions perms
    ,sys.database_principals prin
WHERE perms.grantee_principal_id = prin.principal_id
    AND permission_name = 'VIEW DEFINITION'
    AND grantee_principal_id NOT IN (
        DATABASE_PRINCIPAL_ID('guest')
        ,DATABASE_PRINCIPAL_ID('public')
        )
    AND perms.class = 0
    AND [state] IN ('G','W')

Remediation:

Revoke permissions from principals where not needed. It is recommended to have at most 1 principal granted a specific permission.

REVOKE VIEW DEFINITION FROM [$0]

VA2051: Minimal set of principals should be granted database-scoped VIEW DEFINITION permissions on schema

Every SQL Server securable has permissions associated with it that can be granted to principals. Permissions can be scoped at the server level (assigned to logins and server roles) or at the database level (assigned to database users and database roles). These rules check that only a minimal set of principals are granted database-scoped VIEW DEFINITION permissions on schema.

Rationale:

Developing an application using a least-privileged user account (LUA) approach is an important part of a defensive, in-depth strategy for countering security threats. The LUA approach ensures that users follow the principle of least privilege and always log on with limited user accounts. Administrative tasks are broken out using fixed server roles, and the use of the sysadmin fixed server role is severely restricted. Always follow the principle of least privilege when granting permissions to database users. Grant the minimum permissions necessary to a user or role to accomplish a given task. See https://msdn.microsoft.com/en-us/library/bb669084(v=vs.110).aspx.

Query:

SELECT perms.class_desc AS [Permission Class]
    ,object_schema_name(major_id) AS [Schema Name]
    ,object_name(major_id) AS [Object]
    ,perms.permission_name AS Permission
    ,type_desc AS [Principal Type]
    ,prin.name AS Principal
FROM sys.database_permissions perms
    ,sys.database_principals prin
WHERE perms.grantee_principal_id = prin.principal_id
    AND permission_name = 'VIEW DEFINITION'
    AND grantee_principal_id NOT IN (
        DATABASE_PRINCIPAL_ID('guest')
        ,DATABASE_PRINCIPAL_ID('public')
        )
    AND perms.class = 1
    AND [state] IN ('G','W')

Remediation:

Revoke permissions from principals where not needed. It is recommended to have at most 1 principal granted a specific permission.

REVOKE $3 ON [$1].[$2] FROM [$5]

VA2052: Minimal set of principals should be granted database-scoped VIEW DEFINITION permission on various securables

Every SQL Server securable has permissions associated with it that can be granted to principals. Permissions can be scoped at the server level (assigned to logins and server roles) or at the database level (assigned to database users and database roles). These rules check that only a minimal set of principals are granted database-scoped VIEW DEFINITION permission on various securables.

Rationale:

Developing an application using a least-privileged user account (LUA) approach is an important part of a defensive, in-depth strategy for countering security threats. The LUA approach ensures that users follow the principle of least privilege and always log on with limited user accounts. Administrative tasks are broken out using fixed server roles, and the use of the sysadmin fixed server role is severely restricted. Always follow the principle of least privilege when granting permissions to database users. Grant the minimum permissions necessary to a user or role to accomplish a given task. See https://msdn.microsoft.com/en-us/library/bb669084(v=vs.110).aspx.

Query:

SELECT REPLACE(perms.class_desc, '_', ' ') AS [Permission Class], 
    CASE 
        WHEN perms.class=3 THEN schema_name(major_id) -- schema
        WHEN perms.class=4 THEN printarget.name -- principal
        WHEN perms.class=5 THEN asm.name -- assembly
        WHEN perms.class=6 THEN type_name(major_id) -- type
        WHEN perms.class=10 THEN xmlsc.name -- xml schema
        WHEN perms.class=15 THEN msgt.name COLLATE DATABASE_DEFAULT -- message types
        WHEN perms.class=16 THEN svcc.name COLLATE DATABASE_DEFAULT -- service contracts
        WHEN perms.class=17 THEN svcs.name COLLATE DATABASE_DEFAULT -- services
        WHEN perms.class=18 THEN rsb.name COLLATE DATABASE_DEFAULT -- remote service bindings
        WHEN perms.class=19 THEN rts.name COLLATE DATABASE_DEFAULT -- routes
        WHEN perms.class=23 THEN ftc.name -- full text catalog
        WHEN perms.class=24 then sym.name -- symmetric key
        WHEN perms.class=25 then crt.name -- certificate
        WHEN perms.class=26 then asym.name -- assymetric key
        ELSE ''
    END AS [Object],
    perms.permission_name AS Permission, 
    prin.type_desc AS [Principal Type], 
    prin.name AS Principal
FROM sys.database_permissions perms
LEFT JOIN
    sys.database_principals prin
    ON perms.grantee_principal_id = prin.principal_id
LEFT JOIN 
    sys.assemblies asm
    ON perms.major_id = asm.assembly_id
LEFT JOIN 
    sys.xml_schema_collections xmlsc
    ON perms.major_id = xmlsc.xml_collection_id
LEFT JOIN 
    sys.service_message_types msgt
    ON perms.major_id = msgt.message_type_id
LEFT JOIN 
    sys.service_contracts svcc
    ON perms.major_id = svcc.service_contract_id
LEFT JOIN 
    sys.services svcs
    ON perms.major_id = svcs.service_id
LEFT JOIN 
    sys.remote_service_bindings rsb
    ON perms.major_id = rsb.remote_service_binding_id
LEFT JOIN 
    sys.routes rts
    ON perms.major_id = rts.route_id
LEFT JOIN
    sys.database_principals printarget
    ON perms.major_id = printarget.principal_id
LEFT JOIN    
    sys.symmetric_keys sym
    On perms.major_id = sym.symmetric_key_id
LEFT JOIN
    sys.asymmetric_keys asym
    ON perms.major_id = asym.asymmetric_key_id
    LEFT JOIN
    sys.certificates crt
    ON perms.major_id = crt.certificate_id
LEFT JOIN
    sys.fulltext_catalogs ftc
    ON perms.major_id = ftc.fulltext_catalog_id
WHERE  
    permission_name = 'VIEW DEFINITION'
    AND grantee_principal_id NOT IN (DATABASE_PRINCIPAL_ID('guest'), DATABASE_PRINCIPAL_ID('public')) 
    AND class in (3,4,5,6,10,15,16,17,18,19,23,24,25,26)
    AND [state] IN ('G','W')

Remediation:

Revoke permissions from principals where not needed. It is recommended to have at most 1 principal granted a specific permission.

REVOKE $2 ON $0::[$1] FROM [$4]

☝ back to top


Severity: Low

VA1054: Excessive permissions should not be granted to PUBLIC role on objects or columns

Every SQL Server login belongs to the public server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. This rule displays a list of all securable objects or columns that are accessible to all users through the PUBLIC role.

Rationale:

Database Roles are the basic building block at the heart of separation of duties and the principle of least permission. Granting permissions to principals through the default PUBLIC role defeats this purpose.

Query:

SELECT permission_name
    ,schema_name
    ,object_name
FROM (
    SELECT objs.type AS object_type
        ,schema_name(schema_id) AS schema_name
        ,objs.name AS object_name
        ,user_name(grantor_principal_id) AS grantor_principal_name
        ,permission_name
        ,perms.type
        ,STATE
    FROM sys.database_permissions perms
        ,sys.objects objs
    WHERE objs.object_id = perms.major_id
        AND perms.class = 1 -- objects or columns. Other cases are handled by VA1095 which has different remediation syntax
        AND grantee_principal_id = DATABASE_PRINCIPAL_ID('public')
        AND [state] IN ('G','W')
    UNION
    
    SELECT 'system_object' AS object_type
        ,'sys' AS schema_name
        ,object_name(major_id) AS object_name
        ,user_name(grantor_principal_id) AS grantor_principal_name
        ,permission_name
        ,type
        ,STATE
    FROM sys.database_permissions
    WHERE class = 1
        AND grantee_principal_id = DATABASE_PRINCIPAL_ID('public')
        AND major_id < 0
        AND [state] IN ('G','W')
    ) t
WHERE NOT (
        (
            permission_name = 'EXECUTE'
            AND type = 'EX  '
            AND STATE = 'G'
            AND grantor_principal_name = 'dbo'
            AND schema_name = 'dbo'
            AND (
                (
                    object_type = 'FN'
                    AND object_name IN (
                        'fn_sysdac_get_currentusername'
                        ,'fn_sysdac_get_username'
                        ,'fn_sysdac_is_currentuser_sa'
                        ,'fn_sysdac_is_dac_creator'
                        ,'fn_sysdac_is_login_creator'
                        ,'fn_syspolicy_is_automation_enabled'
                        )
                    )
                OR (
                    object_type = 'P'
                    AND object_name IN (
                        'sp_sysdac_add_history_entry'
                        ,'sp_sysdac_add_instance'
                        ,'sp_sysdac_delete_history'
                        ,'sp_sysdac_delete_instance'
                        ,'sp_sysdac_drop_database'
                        ,'sp_sysdac_ensure_dac_creator'
                        ,'sp_sysdac_rename_database'
                        ,'sp_sysdac_resolve_pending_entry'
                        ,'sp_sysdac_rollback_all_pending_objects'
                        ,'sp_sysdac_rollback_committed_step'
                        ,'sp_sysdac_rollback_pending_object'
                        ,'sp_sysdac_setreadonly_database'
                        ,'sp_sysdac_update_history_entry'
                        ,'sp_sysdac_update_instance'
                        ,'sp_sysdac_upgrade_instance'
                        )
                    )
                )
            )
        OR (
            permission_name = 'EXECUTE'
            AND type = 'EX  '
            AND STATE = 'G'
            AND grantor_principal_name = 'dbo'
            AND schema_name = 'sys'
            AND object_type = 'system_object'
            AND (
                object_name IN (
                    'fn_cColvEntries_80'
                    ,'fn_cdc_check_parameters'
                    ,'fn_cdc_decrement_lsn'
                    ,'fn_cdc_get_column_ordinal'
                    ,'fn_cdc_get_max_lsn'
                    ,'fn_cdc_get_min_lsn'
                    ,'fn_cdc_has_column_changed'
                    ,'fn_cdc_hexstrtobin'
                    ,'fn_cdc_increment_lsn'
                    ,'fn_cdc_is_bit_set'
                    ,'fn_cdc_map_lsn_to_time'
                    ,'fn_cdc_map_time_to_lsn'
                    ,'fn_fIsColTracked'
                    ,'fn_GetCurrentPrincipal'
                    ,'fn_GetRowsetIdFromRowDump'
                    ,'fn_hadr_backup_is_preferred_replica'
                    ,'fn_hadr_is_primary_replica'
                    ,'fn_hadr_is_same

Remediation:

Revoke unnecessary permissions granted to PUBLIC

REVOKE $0 ON [$1].[$2] FROM PUBLIC

VA1069: Permissions to select from system tables and views should be revoked from non-sysadmins

Non-sysadmin permissions to select from system tables and views exist out of the box, and are typically used by tools such as SSMS. This rule enumerates which system tables and views can be accessed by non-sysadmins.

Rationale:

Some organizations prefer to limit such permissions to minimize information gathering in case of a successful SQL injection attack on their systems. Use the output of this rule to create a baseline of acceptable permissions on system views

Query:

SELECT [schema_or_class] AS [Schema]
    ,[object_name] AS [Object]
    ,[grantee_principal_name] AS [Principal]
FROM (
    SELECT isnull(SCHEMA_NAME(objs.schema_id),'sys') AS [schema_or_class]
        ,Object_name(major_id) AS object_name
        ,User_name(grantee_principal_id) AS grantee_principal_name
    FROM sys.database_permissions perms
    LEFT JOIN sys.all_objects objs ON objs.object_id = perms.major_id 
    WHERE perms.class = 1
        AND perms.type = 'SL'
        AND major_id < 0
        AND [state] IN ('G','W')
        AND NOT Object_name(major_id) IN (
            'all_columns'
            ,'all_objects'
            ,'all_parameters'
            ,'all_sql_modules'
            ,'all_views'
            ,'allocation_units'
            ,'assemblies'
            ,'assembly_files'
            ,'assembly_modules'
            ,'assembly_references'
            ,'assembly_types'
            ,'asymmetric_keys'
            ,'certificates'
            ,'change_tracking_tables'
            ,'check_constraints'
            ,'column_encryption_key_values'
            ,'column_encryption_keys'
            ,'column_master_keys'
            ,'column_store_dictionaries'
            ,'column_store_row_groups'
            ,'column_store_segments'
            ,'column_type_usages'
            ,'column_xml_schema_collection_usages'
            ,'columns'
            ,'computed_columns'
            ,'conversation_endpoints'
            ,'conversation_groups'
            ,'conversation_priorities'
            ,'crypt_properties'
            ,'data_spaces'
            ,'database_audit_specification_details'
            ,'database_audit_specifications'
            ,'database_credentials'
            ,'database_files'
            ,'database_permissions'
            ,'database_principals'
            ,'database_role_members'
            ,'database_scoped_configurations'
            ,'database_scoped_credentials'
            ,'default_constraints'
            ,'destination_data_spaces'
            ,'dm_db_column_store_row_group_physical_stats'
            ,'event_notifications'
            ,'events'
            ,'extended_procedures'
            ,'extended_properties'
            ,'external_data_sources'
            ,'external_file_formats'
            ,'external_tables'
            ,'filegroups'
            ,'filetable_system_defined_objects'
            ,'filetables'
            ,'foreign_key_columns'
            ,'foreign_keys'
            ,'fulltext_catalogs'
            ,'fulltext_index_catalog_usages'
            ,'fulltext_index_columns'
            ,'fulltext_index_fragments'
            ,'fulltext_indexes'
            ,'fulltext_stoplists'
            ,'fulltext_stopwords'
            ,'function_order_columns'
            ,'hash_indexes'
            ,'identity_columns'
            ,'index_columns'
            ,'indexes'
            ,'internal_partitions'
            ,'internal_tables'
            ,'key_constraints'
            ,'key_encryptions'
            ,'masked_columns'
            ,'memory_optimized_tables_internal_attributes'
            ,'message_type_xml_schema_collection_usages'
            ,'module_assembly_usages'
            ,'numbered_procedure_parameters'
            ,'numbered_procedures'
            ,'objects'
            ,'parameter_type_usages'
            ,'parameter_xml_schema_collection_usages'
            ,'parameters'
            ,'partition_functions'
            ,'partition_parameters'
            ,'partition_range_values'
            ,'partition_schemes'
            ,'partitions'
            ,'periods'
            ,'plan_guides'
            ,'procedures'
            ,'query_context_settings'
            ,'query_store_plan'
            ,'query_store_query'
            ,'query_store_query_text'
            ,'query_store_runtime_stats'
            ,'query_store_runtime_stats_interval'
            ,'reg

Remediation:

Revoke unnecessary permissions to system tables

REVOKE SELECT ON OBJECT::[$0].[$1] FROM [$2]

VA1070: Database users shouldn’t share the same name as a server login.

Database users may share the same name as a server login. This rule validates that there are no such users.

Rationale:

Logins are created at the server level, while users are created at the database level. There are different types of users. Users with login are mapped to server level logins. Users with passwords can be created on a specific database, but are not mapped to server logins. This rule checks that users with passwords do not have the same name as any SQL login, as that may cause confusion.

Query:

SELECT dp.NAME AS [Principal] 
FROM   sys.database_principals dp, 
       sys.server_principals sp 
WHERE  dp.NAME = sp.NAME COLLATE database_default 
       AND dp.sid != sp.sid 
       AND dp.authentication_type = 2 
ORDER  BY dp.NAME

Remediation:

You must rename the affected users or logins to avoid the confusion, updating all affected applications as well.

VA1094: Database permissions shouldn’t be granted directly to principals

Permissions are rules associated with a securable object to regulate which users can gain access to the object. This rule checks that there are no DB permissions granted directly to users.

Rationale:

Individuals change organizations & job descriptions over time. It is highly recommended to use a centralized access control management through AD group membership.

Query:

SELECT permission_name                  AS [Permission], 
       Replace(dp.class_desc, '_', ' ') AS [Permission Class], 
       CASE 
         WHEN dp.class = 0 THEN Db_name() -- database  
         WHEN dp.class = 3 THEN Schema_name(major_id) -- schema   
         WHEN dp.class = 4 THEN printarget.NAME -- principal   
         WHEN dp.class = 5 THEN asm.NAME -- assembly   
         WHEN dp.class = 6 THEN Type_name(major_id) -- type   
         WHEN dp.class = 10 THEN xmlsc.NAME -- xml schema   
         WHEN dp.class = 15 THEN msgt.NAME COLLATE database_default -- message types   
         WHEN dp.class = 16 THEN svcc.NAME COLLATE database_default -- service contracts   
         WHEN dp.class = 17 THEN svcs.NAME COLLATE database_default -- services   
         WHEN dp.class = 18 THEN rsb.NAME COLLATE database_default -- remote service bindings   
         WHEN dp.class = 19 THEN rts.NAME COLLATE database_default -- routes   
         WHEN dp.class = 23 THEN ftc.NAME -- full text catalog   
         WHEN dp.class = 24 THEN sym.NAME -- symmetric key   
         WHEN dp.class = 25 THEN crt.NAME -- certificate   
         WHEN dp.class = 26 THEN asym.NAME -- assymetric key   
       END                              AS [Object], 
       prin.NAME                        AS [Principal] 
FROM   sys.database_permissions AS dp 
       LEFT JOIN sys.database_principals prin 
              ON dp.grantee_principal_id = prin.principal_id 
       LEFT JOIN sys.assemblies asm 
              ON dp.major_id = asm.assembly_id 
       LEFT JOIN sys.xml_schema_collections xmlsc 
              ON dp.major_id = xmlsc.xml_collection_id 
       LEFT JOIN sys.service_message_types msgt 
              ON dp.major_id = msgt.message_type_id 
       LEFT JOIN sys.service_contracts svcc 
              ON dp.major_id = svcc.service_contract_id 
       LEFT JOIN sys.services svcs 
              ON dp.major_id = svcs.service_id 
       LEFT JOIN sys.remote_service_bindings rsb 
              ON dp.major_id = rsb.remote_service_binding_id 
       LEFT JOIN sys.routes rts 
              ON dp.major_id = rts.route_id 
       LEFT JOIN sys.database_principals printarget 
              ON dp.major_id = printarget.principal_id 
       LEFT JOIN sys.symmetric_keys sym 
              ON dp.major_id = sym.symmetric_key_id 
       LEFT JOIN sys.asymmetric_keys asym 
              ON dp.major_id = asym.asymmetric_key_id 
       LEFT JOIN sys.certificates crt 
              ON dp.major_id = crt.certificate_id 
       LEFT JOIN sys.fulltext_catalogs ftc 
              ON dp.major_id = ftc.fulltext_catalog_id 
WHERE  ( prin.type = 'S' 
          OR prin.type = 'W' ) 
       AND dp.type != 'CO' 
       AND prin.NAME NOT IN ( '##MS_PolicyEventProcessingLogin##', 
                              '##MS_PolicyTsqlExecutionLogin##' ) 
       AND dp.class != 1 
       AND [state] IN ('G','W')
ORDER  BY class_desc, 
          Object_name(major_id), 
          permission_name, 
          prin.NAME;

Remediation:

Revoke permissions granted to users directly. Instead use Windows groups or server roles to grant permissions, and manage role memberships instead.

REVOKE $0 ON $1::[$2] FROM [$3]

VA1096: Principal GUEST should not be granted permissions in the database

Each database includes a user called GUEST. Permissions granted to GUEST are inherited by users who have access to the database, but who do not have a user account in the database. This rule checks that all permissions have been revoked from the GUEST user.

Rationale:

The special user GUEST is used to map any logins that are not mapped to a specific database user. This can result in principals gaining access to a database without having been explicitly granted permission to do so.

Query:

SELECT perms.permission_name AS Permission
FROM sys.database_permissions perms
    ,sys.database_principals prin
WHERE perms.grantee_principal_id = prin.principal_id
    AND grantee_principal_id = DATABASE_PRINCIPAL_ID('guest')
    AND perms.class = 0
    AND [state] IN ('G','W')

Remediation:

Revoke any unnecessary permissions granted to the special account GUEST

REVOKE $0 FROM GUEST

VA1097: Principal GUEST should not be granted permissions on objects or columns

Each database includes a user called GUEST. Permissions granted to GUEST are inherited by users who have access to the database, but who do not have a user account in the database. This rule checks that all permissions have been revoked from the GUEST user.

Rationale:

The special user GUEST is used to map any logins that are not mapped to a specific database user. This can result in principals gaining access to a database without having been explicitly granted permission to do so.

Query:

SELECT object_schema_name(major_id) AS [Schema Name]
    ,object_name(major_id) AS [Object]
    ,perms.permission_name AS Permission
FROM sys.database_permissions perms
    ,sys.database_principals prin
WHERE perms.grantee_principal_id = prin.principal_id
    AND grantee_principal_id = DATABASE_PRINCIPAL_ID('guest')
    AND perms.class = 1
    AND [state] IN ('G','W')

Remediation:

Revoke any unnecessary permissions granted to the special account GUEST

REVOKE $2 ON [$0].[$1] FROM GUEST

VA1099: GUEST user should not be granted permissions on database securables

Each database includes a user called GUEST. Permissions granted to GUEST are inherited by users who have access to the database, but who do not have a user account in the database. This rule checks that all permissions have been revoked from the GUEST user.

Rationale:

The special user GUEST is used to map any logins that are not mapped to a specific database user. This can result in principals gaining access to a database without having been explicitly granted permission to do so.

Query:

SELECT REPLACE(perms.class_desc, '_', ' ') AS [Permission Class], 
    CASE 
        WHEN perms.class=3 THEN schema_name(major_id) -- schema
        WHEN perms.class=4 THEN printarget.name -- principal
        WHEN perms.class=5 THEN asm.name -- assembly
        WHEN perms.class=6 THEN type_name(major_id) -- type
        WHEN perms.class=10 THEN xmlsc.name -- xml schema
        WHEN perms.class=15 THEN msgt.name COLLATE DATABASE_DEFAULT -- message types
        WHEN perms.class=16 THEN svcc.name COLLATE DATABASE_DEFAULT -- service contracts
        WHEN perms.class=17 THEN svcs.name COLLATE DATABASE_DEFAULT -- services
        WHEN perms.class=18 THEN rsb.name COLLATE DATABASE_DEFAULT -- remote service bindings
        WHEN perms.class=19 THEN rts.name COLLATE DATABASE_DEFAULT -- routes
        WHEN perms.class=23 THEN ftc.name -- full text catalog
        WHEN perms.class=24 then sym.name -- symmetric key
        WHEN perms.class=25 then crt.name -- certificate
        WHEN perms.class=26 then asym.name -- assymetric key
    END AS [Object],
    perms.permission_name AS Permission
FROM sys.database_permissions perms
LEFT JOIN
    sys.database_principals prin
    ON perms.grantee_principal_id = prin.principal_id
LEFT JOIN 
    sys.assemblies asm
    ON perms.major_id = asm.assembly_id
LEFT JOIN 
    sys.xml_schema_collections xmlsc
    ON perms.major_id = xmlsc.xml_collection_id
LEFT JOIN 
    sys.service_message_types msgt
    ON perms.major_id = msgt.message_type_id
LEFT JOIN 
    sys.service_contracts svcc
    ON perms.major_id = svcc.service_contract_id
LEFT JOIN 
    sys.services svcs
    ON perms.major_id = svcs.service_id
LEFT JOIN 
    sys.remote_service_bindings rsb
    ON perms.major_id = rsb.remote_service_binding_id
LEFT JOIN 
    sys.routes rts
    ON perms.major_id = rts.route_id
LEFT JOIN
    sys.database_principals printarget
    ON perms.major_id = printarget.principal_id
LEFT JOIN    
    sys.symmetric_keys sym
    On perms.major_id = sym.symmetric_key_id
LEFT JOIN
    sys.asymmetric_keys asym
    ON perms.major_id = asym.asymmetric_key_id
    LEFT JOIN
    sys.certificates crt
    ON perms.major_id = crt.certificate_id
LEFT JOIN
    sys.fulltext_catalogs ftc
    ON perms.major_id = ftc.fulltext_catalog_id
WHERE  
    grantee_principal_id = DATABASE_PRINCIPAL_ID('guest') 
    AND class in (3,4,5,6,10,15,16,17,18,19,23,24,25,26)
    AND [state] IN ('G','W')

Remediation:

Revoke any unnecessary permissions granted to the special account GUEST

REVOKE $2 ON $0::[$1] FROM GUEST

VA1246: Application roles should not be used

An application role is a database principal that enables an application to run with its own user-like permissions. Application roles enable that only users connecting through a particular application can access specific data. Application roles are password-based (which applications typically hardcode) and not permission based, which exposes the database to approle impersonation by password-guessing. This rule checks that no application roles are defined in the database.

Rationale:

It is important to limit the possibility of acquiring user-like permissions to the database, and since application roles are password based, they can lead to impersonation of the application role via password-guessing.

Query:

SELECT name
FROM sys.database_principals 
WHERE type  = 'A' 
ORDER BY name

Remediation:

Remove the application roles. Use users without login as a permission-based replacement

DROP APPLICATION ROLE [$0]

VA1282: Orphan roles should be removed

Orphan roles are user-defined roles that have no members. It is recommended to eliminate orphaned roles as they are not needed on the system. This rule checks whether there are any orphan roles

Rationale:

Reduce the attack surface area by eliminating unnecessary roles in the system.

Query:

SELECT name FROM sys.database_principals 
WHERE type = 'R' 
AND principal_id not in (0,16384,16385,16386,16387,16389,16390,16391,16392,16393) 
AND principal_id not in ( SELECT distinct role_principal_id 
FROM sys.database_role_members )

Remediation:

Drop the unnecessary roles.

DROP ROLE [$0]

VA1286: Database permissions shouldn’t be granted directly to principals (OBJECT or COLUMN)

Permissions are rules associated with an securable object to regulate which users can gain access to the object. This rule checks that there are no DB permissions granted directly to users.

Rationale:

Individuals change organizations & job descriptions over time, it is highly recommended to use a centralized access control management through AD group membership.

Query:

SELECT permission_name AS [Permission]
    ,Schema_name(objs.schema_id) AS [Schema]
    ,objs.NAME AS [Object]
    ,prin.NAME AS [Principal]
FROM sys.database_permissions AS dp
LEFT JOIN sys.all_objects AS objs ON objs.object_id = dp.major_id
LEFT JOIN sys.database_principals prin ON dp.grantee_principal_id = prin.principal_id
WHERE (
        prin.type = 'S'
        OR prin.type = 'W'
        )
    AND dp.type != 'CO'
    AND prin.NAME NOT IN (
        '##MS_PolicyEventProcessingLogin##'
        ,'##MS_PolicyTsqlExecutionLogin##'
        )
    AND dp.class = 1
    AND [state] IN ('G','W')
ORDER BY class_desc
    ,Object_name(major_id)
    ,permission_name
    ,prin.NAME;

Remediation:

Revoke permissions granted to users directly. Instead use Windows groups or server roles to grant permissions, and manage role memberships instead.

REVOKE $0 ON [$1].[$2] FROM [$3]

VA2030: Minimal set of principals should be granted database-scoped SELECT or EXECUTE permissions

Every SQL Server securable has permissions associated with it that can be granted to principals. Permissions can be scoped at the server level (assigned to logins and server roles) or at the database level (assigned to database users and database roles). These rules check that only a minimal set of principals are granted database-scoped SELECT or EXECUTE permissions.

Rationale:

Developing an application using a least-privileged user account (LUA) approach is an important part of a defensive, in-depth strategy for countering security threats. The LUA approach ensures that users follow the principle of least privilege and always log on with limited user accounts. Administrative tasks are broken out using fixed server roles, and the use of the sysadmin fixed server role is severely restricted. Always follow the principle of least privilege when granting permissions to database users. Grant the minimum permissions necessary to a user or role to accomplish a given task. See https://msdn.microsoft.com/en-us/library/bb669084(v=vs.110).aspx.

Query:

SELECT perms.class_desc as [Permission Class], perms.permission_name AS Permission, type_desc AS [Principal Type], prin.name as Principal
FROM sys.database_permissions perms, sys.database_principals prin
WHERE perms.grantee_principal_id = prin.principal_id 
    AND permission_name IN ('SELECT', 'EXECUTE')
    AND grantee_principal_id NOT IN (DATABASE_PRINCIPAL_ID('guest'), DATABASE_PRINCIPAL_ID('public')) 
    AND perms.class = 0
    AND [state] IN ('G','W')
    AND NOT (prin.type = 'C' AND prin.name = '##MS_AgentSigningCertificate##' 
        AND perms.class = 0 AND perms.type = 'EX' 
        AND user_name(grantor_principal_id) = 'dbo'
        AND major_id = 0)

Remediation:

Revoke permissions from principals where not needed. It is recommended to have at most 1 principal granted a specific permission.

REVOKE $1 FROM [$3]

VA2031: Minimal set of principals should be granted database-scoped SELECT permission on objects or columns

Every SQL Server securable has permissions associated with it that can be granted to principals. Permissions can be scoped at the server level (assigned to logins and server roles) or at the database level (assigned to database users and database roles). These rules check that only a minimal set of principals are granted database-scoped SELECT permission on objects or columns.

Rationale:

Developing an application using a least-privileged user account (LUA) approach is an important part of a defensive, in-depth strategy for countering security threats. The LUA approach ensures that users follow the principle of least privilege and always log on with limited user accounts. Administrative tasks are broken out using fixed server roles, and the use of the sysadmin fixed server role is severely restricted. Always follow the principle of least privilege when granting permissions to database users. Grant the minimum permissions necessary to a user or role to accomplish a given task. See https://msdn.microsoft.com/en-us/library/bb669084(v=vs.110).aspx.

Query:

SELECT perms.class_desc AS [Permission Class]
    ,object_schema_name(major_id) AS [Schema Name]
    ,object_name(major_id) AS [Object]
    ,perms.permission_name AS Permission
    ,type_desc AS [Principal Type]
    ,prin.name AS Principal
FROM sys.database_permissions perms
    ,sys.database_principals prin
WHERE perms.grantee_principal_id = prin.principal_id
    AND permission_name = 'SELECT'
	AND [state] IN ('G','W')
    AND user_name(grantee_principal_id) NOT IN (
        'guest'
        ,'public'
        )
    AND perms.class = 1
    AND NOT (
        state_desc = 'GRANT'
        AND (
            (
                prin.type = 'R'
                AND prin.name = 'loginmanager'
                AND user_name(grantor_principal_id) = 'sys'
                AND object_name(major_id) IN ('sql_logins')
                )
            OR (
                prin.type = 'R'
                AND prin.name = 'public'
                AND user_name(grantor_principal_id) = 'sys'
                AND object_name(major_id) IN (
                    'bandwidth_usage'
                    ,'database_connection_stats'
                    ,'database_error_stats'
                    ,'database_firewall_rules'
                    ,'database_usage'
                    ,'dm_database_copies'
                    ,'elastic_pool_resource_stats'
                    ,'event_log'
                    ,'firewall_rules'
                    ,'geo_replication_links'
                    ,'resource_stats'
                    )
                )
            OR (
                prin.type = 'R'
                AND prin.name = 'public'
                AND user_name(grantor_principal_id) = 'dbo'
                AND object_name(major_id) IN (
                    'all_columns'
                    ,'all_objects'
                    ,'all_parameters'
                    ,'all_sql_modules'
                    ,'all_views'
                    ,'allocation_units'
                    ,'assemblies'
                    ,'assembly_files'
                    ,'assembly_modules'
                    ,'assembly_references'
                    ,'assembly_types'
                    ,'asymmetric_keys'
                    ,'availability_databases_cluster'
                    ,'availability_group_listener_ip_addresses'
                    ,'availability_group_listeners'
                    ,'availability_groups'
                    ,'availability_groups_cluster'
                    ,'availability_read_only_routing_lists'
                    ,'availability_replicas'
                    ,'backup_devices'
                    ,'certificates'
                    ,'change_tracking_databases'
                    ,'change_tracking_tables'
                    ,'check_constraints'
                    ,'COLUMN_DOMAIN_USAGE'
                    ,'column_encryption_key_values'
                    ,'column_encryption_keys'
                    ,'column_master_key_definitions'
                    ,'column_master_keys'
                    ,'COLUMN_PRIVILEGES'
                    ,'column_store_dictionaries'
                    ,'column_store_row_groups'
                    ,'column_store_segments'
                    ,'column_type_usages'
                    ,'column_xml_schema_collection_usages'
                    ,'columns'
                    ,'computed_columns'
                    ,'configurations'
                    ,'CONSTRAINT_COLUMN_USAGE'
                    ,'CONSTRAINT_TABLE_USAGE'
                    ,'conversation_endpoints'
                    ,'conversation_groups'
                    ,'conversation_priorities'
                    ,'credentials'
                    ,'crypt_properties'
                    ,'cryptographic_providers'
                    ,'data_spaces'
                    ,'database_audit_specification_details'
                    ,'database_audit_specifications'
                    

Remediation:

Revoke permissions from principals where not needed. It is recommended to have at most 1 principal granted a specific permission.

REVOKE $3 ON [$1].[$2] FROM [$5]

VA2032: Minimal set of principals should be granted database-scoped SELECT or EXECUTE permissions on schema

Every SQL Server securable has permissions associated with it that can be granted to principals. Permissions can be scoped at the server level (assigned to logins and server roles) or at the database level (assigned to database users and database roles). These rules check that only a minimal set of principals are granted database-scoped SELECT or EXECUTE permissions on schema.

Rationale:

Developing an application using a least-privileged user account (LUA) approach is an important part of a defensive, in-depth strategy for countering security threats. The LUA approach ensures that users follow the principle of least privilege and always log on with limited user accounts. Administrative tasks are broken out using fixed server roles, and the use of the sysadmin fixed server role is severely restricted. Always follow the principle of least privilege when granting permissions to database users. Grant the minimum permissions necessary to a user or role to accomplish a given task. See https://msdn.microsoft.com/en-us/library/bb669084(v=vs.110).aspx.

Query:

select perms.class_desc AS [Permission Class], 
        schema_name(major_id) AS [Object],
        perms.permission_name AS Permission, 
        prin.type_desc AS [Principal Type], 
        prin.name AS Principal
FROM sys.database_permissions perms
LEFT JOIN
    sys.database_principals prin
    ON perms.grantee_principal_id = prin.principal_id
WHERE  
    perms.class = '3' 
    AND grantee_principal_id NOT IN (DATABASE_PRINCIPAL_ID('guest'), DATABASE_PRINCIPAL_ID('public')) 
    AND permission_name IN ('SELECT', 'EXECUTE')
    AND [state] IN ('G','W')

Remediation:

Revoke permissions from principals where not needed. It is recommended to have at most 1 principal granted a specific permission.

REVOKE $2 ON $0::[$1] FROM [$4]

VA2033: Minimal set of principals should be granted database-scoped EXECUTE permission on objects or columns

Every SQL Server securable has permissions associated with it that can be granted to principals. Permissions can be scoped at the server level (assigned to logins and server roles) or at the database level (assigned to database users and database roles). These rules check that only a minimal set of principals are granted database-scoped EXECUTE permission on objects or columns.

Rationale:

Developing an application using a least-privileged user account (LUA) approach is an important part of a defensive, in-depth strategy for countering security threats. The LUA approach ensures that users follow the principle of least privilege and always log on with limited user accounts. Administrative tasks are broken out using fixed server roles, and the use of the sysadmin fixed server role is severely restricted. Always follow the principle of least privilege when granting permissions to database users. Grant the minimum permissions necessary to a user or role to accomplish a given task. See https://msdn.microsoft.com/en-us/library/bb669084(v=vs.110).aspx.

Query:

SELECT perms.class_desc as [Permission Class], object_schema_name(major_id) as [Schema Name], object_name(major_id) as [Object], perms.permission_name AS Permission, type_desc AS [Principal Type], prin.name as Principal
FROM sys.database_permissions perms, sys.database_principals prin
WHERE perms.grantee_principal_id = prin.principal_id 
    AND permission_name IN ('EXECUTE') 
    AND grantee_principal_id NOT IN (DATABASE_PRINCIPAL_ID('guest'), DATABASE_PRINCIPAL_ID('public')) 
    AND perms.class = 1
    AND [state] IN ('G','W')
    AND NOT ((prin.type = 'R' AND prin.name = 'public' AND user_name(grantor_principal_id) = 'dbo' AND state_desc = 'GRANT' AND object_name(major_id) IN (
        'fn_cColvEntries_80', 'fn_cdc_check_parameters', 'fn_cdc_decrement_lsn', 'fn_cdc_get_column_ordinal', 'fn_cdc_get_max_lsn', 'fn_cdc_get_min_lsn', 'fn_cdc_has_column_changed', 'fn_cdc_hexstrtobin', 'fn_cdc_increment_lsn', 'fn_cdc_is_bit_set', 'fn_cdc_map_lsn_to_time', 'fn_cdc_map_time_to_lsn', 'fn_fIsColTracked', 'fn_GetCurrentPrincipal', 'fn_GetRowsetIdFromRowDump', 'fn_hadr_backup_is_preferred_replica', 'fn_hadr_is_primary_replica', 'fn_hadr_is_same_replica', 'fn_IsBitSetInBitmask', 'fn_isrolemember', 'fn_MapSchemaType', 'fn_MSdayasnumber', 'fn_MSgeneration_downloadonly', 'fn_MSget_dynamic_filter_login', 'fn_MSorbitmaps', 'fn_MSrepl_map_resolver_clsid', 'fn_MStestbit', 'fn_MSvector_downloadonly', 'fn_numberOf1InBinaryAfterLoc', 'fn_numberOf1InVarBinary', 'fn_PhysLocFormatter', 'fn_repl_hash_binary', 'fn_repladjustcolumnmap', 'fn_repldecryptver4', 'fn_replformatdatetime', 'fn_replgetparsedddlcmd', 'fn_replp2pversiontotranid', 'fn_replreplacesinglequote', 'fn_replreplacesinglequoteplusprotectstring', 'fn_repluniquename', 'fn_replvarbintoint', 'fn_sqlvarbasetostr', 'fn_sysdac_get_currentusername', 'fn_sysdac_get_username', 'fn_sysdac_is_currentuser_sa', 'fn_sysdac_is_dac_creator', 'fn_sysdac_is_login_creator', 'fn_syspolicy_is_automation_enabled', 'fn_varbintohexstr', 'fn_varbintohexsubstring', 'fn_yukonsecuritymodelrequired', 'GeographyCollectionAggregate', 'GeographyConvexHullAggregate', 'GeographyEnvelopeAggregate', 'GeographyUnionAggregate', 'GeometryCollectionAggregate', 'GeometryConvexHullAggregate', 'GeometryEnvelopeAggregate', 'GeometryUnionAggregate', 'ORMask', 'sp_add_agent_parameter', 'sp_add_agent_profile', 'sp_add_log_shipping_alert_job', 'sp_add_log_shipping_primary_database', 'sp_add_log_shipping_primary_secondary', 'sp_add_log_shipping_secondary_database', 'sp_add_log_shipping_secondary_primary', 'sp_addapprole', 'sp_addarticle', 'sp_adddatatype', 'sp_adddatatypemapping', 'sp_adddistpublisher', 'sp_adddistributiondb', 'sp_adddistributor', 'sp_adddynamicsnapshot_job', 'sp_addextendedproperty', 'sp_AddFunctionalUnitToComponent', 'sp_addlinkedserver', 'sp_addlinkedsrvlogin', 'sp_addlogin', 'sp_addlogreader_agent', 'sp_addmergealternatepublisher', 'sp_addmergearticle', 'sp_addmergefilter', 'sp_addmergelogsettings', 'sp_addmergepartition', 'sp_addmergepublication', 'sp_addmergepullsubscription', 'sp_addmergepullsubscription_agent', 'sp_addmergepushsubscription_agent', 'sp_addmergesubscription', 'sp_addmessage', 'sp_addpublication', 'sp_addpublication_snapshot', 'sp_addpullsubscription', 'sp_addpullsubscription_agent', 'sp_addpushsubscription_agent', 'sp_addqreader_agent', 'sp_addqueued_artinfo', 'sp_addremotelogin', 'sp_addrole', 'sp_addrolemember', 'sp_addscriptexec', 'sp_addserver', 'sp_addsrvrolemember', 'sp_addsubscriber', 'sp_addsubscriber_schedule', 'sp_addsubscription', 'sp_addsynctriggers', 'sp_addsynctriggerscore', 'sp_addtabletocontents', 'sp_addtype', 'sp_addumpdevice', 'sp_adduser', 'sp_adjustpublisheridentityrange', 'sp_altermessage', 'sp_approlepassword', 'sp_article_validation', 'sp_articlecolumn', 'sp_articlefilter', 'sp_articleview', 'sp_assemblies_rowset', 'sp_assemblies_rowset_rmt', 'sp_assemblies_rowset2', 'sp_assembly_dependencies_rowset', 'sp_assembly_dependencies_rowset_rmt', 'sp_assembly_dependencies_

Remediation:

Revoke permissions from principals where not needed. It is recommended to have at most 1 principal granted a specific permission.

REVOKE $3 ON [$1].[$2] FROM [$5]

VA2034: Minimal set of principals should be granted database-scoped EXECUTE permission on XML Schema Collection

Every SQL Server securable has permissions associated with it that can be granted to principals. Permissions can be scoped at the server level (assigned to logins and server roles) or at the database level (assigned to database users and database roles). These rules check that only a minimal set of principals are granted database-scoped EXECUTE permission on XML Schema Collection.

Rationale:

Developing an application using a least-privileged user account (LUA) approach is an important part of a defensive, in-depth strategy for countering security threats. The LUA approach ensures that users follow the principle of least privilege and always log on with limited user accounts. Administrative tasks are broken out using fixed server roles, and the use of the sysadmin fixed server role is severely restricted. Always follow the principle of least privilege when granting permissions to database users. Grant the minimum permissions necessary to a user or role to accomplish a given task. See https://msdn.microsoft.com/en-us/library/bb669084(v=vs.110).aspx.

Query:

SELECT REPLACE(perms.class_desc, '_', ' ') AS [Permission Class]
    ,xmlsc.name AS [Object]
    ,perms.permission_name AS Permission
    ,prin.type_desc AS [Principal Type]
    ,prin.name AS Principal
FROM sys.database_permissions perms
LEFT JOIN sys.database_principals prin ON perms.grantee_principal_id = prin.principal_id
LEFT JOIN sys.xml_schema_collections xmlsc ON perms.major_id = xmlsc.xml_collection_id
WHERE permission_name = 'EXECUTE'
    AND [state] IN ('G','W')
    AND perms.class = 10
    AND grantee_principal_id NOT IN (
        DATABASE_PRINCIPAL_ID('guest')
        ,DATABASE_PRINCIPAL_ID('public')
        )
    AND NOT (
        prin.type = 'R'
        AND prin.name = 'dc_admin'
        AND user_name(grantor_principal_id) = 'dbo'
        AND state_desc = 'GRANT'
        )

Remediation:

Revoke permissions from principals where not needed. It is recommended to have at most 1 principal granted a specific permission.

REVOKE $2 ON $0::[$1] FROM [$4]

VA2040: Minimal set of principals should be granted low impact database-scoped permissions

Every SQL Server securable has permissions associated with it that can be granted to principals. Permissions can be scoped at the server level (assigned to logins and server roles) or at the database level (assigned to database users and database roles). These rules check that only a minimal set of principals are granted low impact database-scoped permissions.

Rationale:

Developing an application using a least-privileged user account (LUA) approach is an important part of a defensive, in-depth strategy for countering security threats. The LUA approach ensures that users follow the principle of least privilege and always log on with limited user accounts. Administrative tasks are broken out using fixed server roles, and the use of the sysadmin fixed server role is severely restricted. Always follow the principle of least privilege when granting permissions to database users. Grant the minimum permissions necessary to a user or role to accomplish a given task. See https://msdn.microsoft.com/en-us/library/bb669084(v=vs.110).aspx.

Query:

SELECT perms.class_desc AS [Permission Class]
    ,perms.permission_name AS Permission
    ,type_desc AS [Principal Type]
    ,prin.name AS Principal
FROM sys.database_permissions perms
    ,sys.database_principals prin
WHERE perms.grantee_principal_id = prin.principal_id
    AND permission_name IN (
        'INSERT'
        ,'UPDATE'
        ,'DELETE'
        )
    AND grantee_principal_id NOT IN (
        DATABASE_PRINCIPAL_ID('guest')
        ,DATABASE_PRINCIPAL_ID('public')
        )
    AND perms.class = 0
    AND [state] IN ('G','W')

Remediation:

Revoke permissions from principals where not needed. It is recommended to have at most 1 principal granted a specific permission.

REVOKE $1 FROM [$3]

VA2041: Minimal set of principals should be granted low impact database-scoped permissions on objects or columns

Every SQL Server securable has permissions associated with it that can be granted to principals. Permissions can be scoped at the server level (assigned to logins and server roles) or at the database level (assigned to database users and database roles). These rules check that only a minimal set of principals are granted low impact database-scoped permissions on objects or columns.

Rationale:

Developing an application using a least-privileged user account (LUA) approach is an important part of a defensive, in-depth strategy for countering security threats. The LUA approach ensures that users follow the principle of least privilege and always log on with limited user accounts. Administrative tasks are broken out using fixed server roles, and the use of the sysadmin fixed server role is severely restricted. Always follow the principle of least privilege when granting permissions to database users. Grant the minimum permissions necessary to a user or role to accomplish a given task. See https://msdn.microsoft.com/en-us/library/bb669084(v=vs.110).aspx.

Query:

SELECT perms.class_desc AS [Permission Class]
    ,object_schema_name(major_id) AS [Schema Name]
    ,object_name(major_id) AS [Object]
    ,perms.permission_name AS Permission
    ,type_desc AS [Principal Type]
    ,prin.name AS Principal
FROM sys.database_permissions perms
    ,sys.database_principals prin
WHERE perms.grantee_principal_id = prin.principal_id
    AND permission_name IN (
        'INSERT'
        ,'UPDATE'
        ,'DELETE'
        )
    AND grantee_principal_id NOT IN (
        DATABASE_PRINCIPAL_ID('guest')
        ,DATABASE_PRINCIPAL_ID('public')
        )
    AND perms.class = 1
    AND [state] IN ('G','W')

Remediation:

Revoke permissions from principals where not needed. It is recommended to have at most 1 principal granted a specific permission.

REVOKE $3 ON [$1].[$2] FROM [$5]

VA2042: Minimal set of principals should be granted low impact database-scoped permissions on schema

Every SQL Server securable has permissions associated with it that can be granted to principals. Permissions can be scoped at the server level (assigned to logins and server roles) or at the database level (assigned to database users and database roles). These rules check that only a minimal set of principals are granted low impact database-scoped permissions on schema.

Rationale:

Developing an application using a least-privileged user account (LUA) approach is an important part of a defensive, in-depth strategy for countering security threats. The LUA approach ensures that users follow the principle of least privilege and always log on with limited user accounts. Administrative tasks are broken out using fixed server roles, and the use of the sysadmin fixed server role is severely restricted. Always follow the principle of least privilege when granting permissions to database users. Grant the minimum permissions necessary to a user or role to accomplish a given task. See https://msdn.microsoft.com/en-us/library/bb669084(v=vs.110).aspx.

Query:

SELECT perms.class_desc AS [Permission Class]
    ,schema_name(major_id) AS [Object]
    ,perms.permission_name AS Permission
    ,prin.type_desc AS [Principal Type]
    ,prin.name AS Principal
FROM sys.database_permissions perms
LEFT JOIN sys.database_principals prin ON perms.grantee_principal_id = prin.principal_id
WHERE perms.permission_name IN (
        'INSERT'
        ,'UPDATE'
        ,'DELETE'
        )
    AND grantee_principal_id NOT IN (
        DATABASE_PRINCIPAL_ID('guest')
        ,DATABASE_PRINCIPAL_ID('public')
        )
    AND perms.class = 3
    AND [state] IN ('G','W')

Remediation:

Revoke permissions from principals where not needed. It is recommended to have at most 1 principal granted a specific permission.

REVOKE $2 ON $0::[$1] FROM [$4]

VA2109: Minimal set of principals should be members of fixed low impact database roles

SQL Server provides roles to help manage the permissions. Roles are security principals that group other principals. Database-level roles are database-wide in their permission scope. This rule checks that a minimal set of principals are members of the fixed database roles.

Rationale:

Fixed database roles may have administrative permissions on the system. Following the principle of least privilege, it is important to minimize membership in fixed database roles and keep a baseline of these memberships. See https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles for additional information on database roles.

Query:

SELECT user_name(sr.member_principal_id) as [Principal], user_name(sr.role_principal_id) as [Role], type_desc as [Principal Type], authentication_type_desc as [Authentication Type]
FROM sys.database_role_members sr, sys.database_principals sp   
WHERE sp.principal_id = sr.member_principal_id 
AND sr.role_principal_id IN (user_id('db_datareader'), 
                             user_id('db_datawriter'),
                             user_id('db_denydatareader'),
                             user_id('db_denydatawriter'))
ORDER BY sp.name

Remediation:

Remove members who should not have access to the database role

ALTER ROLE [$1] DROP MEMBER [$0]

☝ back to top


Data Protection – 7 rule(s)

Severity: High

VA1098: Any Existing SSB or Mirroring endpoint should require AES connection

Service Broker and Mirroring endpoints support different encryption algorithms, including no-encryption. This rule checks that any existing endpoint requires AES encryption.

Rationale:

Using a weak encryption algorithm or plaintext in communication protocols can lead to data loss, data manipulation and/or connection hijacking.

Query:

SELECT ep.NAME      AS [Name], 
       ep.type_desc AS [Type] 
FROM   sys.database_mirroring_endpoints dme, 
       sys.endpoints ep 
WHERE  dme.endpoint_id = ep.endpoint_id 
       AND dme.encryption_algorithm  2 
       AND ep.type BETWEEN 3 AND 4 
UNION 
SELECT ep.NAME      AS [Name], 
       ep.type_desc AS [Type] 
FROM   sys.service_broker_endpoints sbe, 
       sys.endpoints ep 
WHERE  sbe.endpoint_id = ep.endpoint_id 
       AND sbe.encryption_algorithm  2 
       AND ep.type BETWEEN 3 AND 4

Remediation:

Change the affected endpoints to accept only AES connections

ALTER ENDPOINT [$0] FOR $1 ( ENCRYPTION = REQUIRED ALGORITHM AES )

VA1221: Database Encryption Symmetric Keys should use AES algorithm

SQL Server uses encryption keys to help secure data, credentials, and connection information that is stored in a server database. SQL Server has two kinds of keys: symmetric and asymmetric. This rule checks that Database Encryption Symmetric Keys use AES algorithm.

Rationale:

Weak encryption algorithms may lead to weaknesses in the data-at-rest protection.

Query:

SELECT db_name(database_id) as db_name, encryption_state, key_algorithm, key_length, encryptor_type 
FROM sys.dm_database_encryption_keys 
WHERE key_algorithm != 'AES' 
ORDER BY db_name(database_id), encryption_state, key_algorithm, key_length, encryptor_type

Remediation:

Regenerate the DEK using AES

VA1222: Cell-Level Encryption keys should use AES algorithm

Cell-Level Encryption (CLE) allows you to encrypt your data using symmetric and asymmetric keys. This rule checks that Cell-Level Encryption symmetric keys use AES algorithm.

Rationale:

Weak encryption algorithms may lead to weaknesses in the data-at-rest protection

Query:

SELECT NAME AS [Name], 
       algorithm_desc AS [Algorithm] 
FROM   sys.symmetric_keys 
WHERE  key_algorithm NOT IN ( 'A1', 'A2', 'A3' ) 
ORDER  BY NAME, 
          algorithm_desc

Remediation:

Create AES keys, re-encrypt the data using the new key, and drop the affected keys.

VA1223: Certificate keys should use at least 2048 bits

Certificate keys are used in RSA and other encryption algorithms to protect data. These keys need to be of enough length to secure the user’s data. This rule checks that the key’s length is at least 2048 bits for all certificates.

Rationale:

Key length defines the upper-bound on the encryption algorithm’s security. Using short keys in encryption algorithms may lead to weaknesses in data-at-rest protection.

Query:

SELECT name, issuer_name, cert_serial_number, subject, thumbprint 
FROM sys.certificates 
WHERE key_length < 2048

Remediation:

Create new certificates, re-encrypt the data/sign-data using the new key, and drop the affected keys.

VA1224: Asymmetric keys’ length should be at least 2048 bits

Database asymmetric keys are used in many encryption algorithms, these keys need to be of enough length to secure the encrypted data, this rule checks that all asymmetric keys stored in the database are of length of at least 2048 bits

Rationale:

Key length defines the upper-bound on the encryption algorithm’s security, using short keys in encryption algorithms may lead to weaknesses in the data-at-rest protection

Query:

SELECT name, pvt_key_encryption_type_desc, algorithm_desc 
FROM sys.asymmetric_keys 
WHERE key_length < 2048 
AND NOT (DB_NAME() = 'master' AND name = 'MS_SQLEnableSystemAssemblyLoadingKey') 
ORDER BY name, pvt_key_encryption_type_desc, algorithm_desc

Remediation:

Create new asymmetric Keys, re-encrypt the data/sign-data using the new key, and drop the affected keys.

DROP ASYMMETRIC KEY [$0]

☝ back to top


Severity: Medium

VA1219: Transparent data encryption should be enabled

Transparent data encryption (TDE) helps protect against the threat of malicious activity by performing real-time encryption and decryption of the database, associated backups, and transaction log files ‘at rest’, without requiring changes to the application. This rule checks that TDE is enabled on the database.

Rationale:

Transparent Data Encryption (TDE) protects data ‘at rest’, meaning the data and log files are encrypted when stored on disk.

Query:

SELECT CASE WHEN EXISTS
( SELECT *
    FROM sys.databases 
    WHERE name = db_name() 
    AND is_encrypted = 0)
THEN 1
ELSE 0
END AS [Violation]

Remediation:

Enable TDE on the affected database. Please follow the instructions on https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption

VA1287: Sensitive data columns should be classified

This rule discovers and characterizes potentially sensitive data in the database. The result is a collection of sensitive database columns, which should be reviewed and classified using SQL Data Discovery & Classification. This allows database columns to be persistently labeled according to their sensitivity, which enables tracking (auditing) the use of classified data and creating reports. If your sensitive database columns are unprotected, you should also consider applying one of SQL Database’s built-in security capabilities to restrict access to and protect your sensitive data.

Rationale:

The data residing in your database can have varying levels of business and privacy sensitivity. It is important to be aware of the location of your most sensitive data elements, so that their access can be monitored and tracked. SQL Data Discovery & Classification enables you to assign a distinct classification label to each database column and persist this information as column metadata within the database. This classification metadata can then be used for tracking and monitoring objectives. In addition, access to sensitive data should be more tightly controlled. Built-in SQL security capabilities like Always Encrypted, Dynamic Data Masking, and Row-Level Security can be used to control access and protect data.

Query:

Remediation:

Review the identified list of potentially sensitive columns and apply classifications where relevant via the Classify Data task of the database. If none of the identified columns actually represent sensitive elements in your database, you can set them as your approved baseline to acknowledge their status and to prevent them from being identified again. In addition, apply column-level data protection security measures where appropriate: Always Encrypted — keeps sensitive data columns encrypted on the server side (‘https://go.microsoft.com/fwlink/?linkid=862688&#8217;) or Dynamic Data Masking — limits sensitive data exposure by dynamically masking it to non-privileged users when data is returned from the server to the client (‘https://go.microsoft.com/fwlink/?linkid=524331&#8217;). You can also use Row Level Security to restrict access to data rows by creating a security policy based on characteristics of the user executing a query (‘https://go.microsoft.com/fwlink/?linkid=862687&#8217;).

☝ back to top


Surface Area Reduction – 8 rule(s)

Severity: High

VA1102: The Trustworthy bit should be disabled on all databases except MSDB

The TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts the database and the contents within it. If this option is enabled, database modules (for example, user-defined functions or stored procedures) that use an impersonation context can access resources outside the database. This rule verifies that the TRUSTWORTHY bit is disabled on all databases, except MSDB.

Rationale:

The trustworthy bit (TWbit) is an access control mechanism that enables features that can lead to an elevation of privilege such as CLR and server-scope impersonation. For more information: http://support.microsoft.com/kb/2183687

Query:

SELECT CASE 
         WHEN EXISTS (SELECT * 
                      FROM   sys.databases 
                      WHERE  NAME = Db_name() 
                             AND is_trustworthy_on = 1) THEN 1 
         ELSE 0 
       END       AS Violation, 
       Db_name() AS [Database]

Remediation:

Disable the trustworthy bit (TWbit) from all affected databases. If you need to use functionality that is controlled by the TWbit, it is recommended to use digital signatures to enable the functionality instead of enabling the TWbit on the database.

ALTER DATABASE [$1] SET TRUSTWORTHY OFF

VA1245: The dbo information should be consistent between the target DB and master

There is redundant information about the dbo identity for any database: metadata stored in the database itself and metadata stored in master DB. This rule checks that this information is consistent between the target DB and master.

Rationale:

Both copies of dbo metadata should match to avoid potential system problems such as permission problems when using some features such as CLR.

Query:

SELECT CASE 
         WHEN EXISTS (SELECT * 
                      FROM   sys.database_principals dbprs, 
                             sys.databases dbs 
                      WHERE  dbprs.sid != dbs.owner_sid 
                             AND dbs.database_id = Db_id() 
                             AND dbprs.principal_id = 1) THEN 1 
         ELSE 0 
       END AS [Violation]

Remediation:

Use ALTER AUTHORIZATION DDL to specify the user that should be the dbo for the database

VA1256: User CLR assemblies should not be defined in the database

CLR assemblies can be used to execute arbitrary code on SQL Server process. This rule checks that there are no user-defined CLR assemblies in the database

Rationale:

Using CLR assemblies can bring a security flaw to the SQL Server instance and to all other network resources accessible from it

Query:

SELECT name FROM sys.assemblies WHERE is_user_defined != 0

Remediation:

Drop assemblies from the affected databases

DROP ASSEMBLY [$0]

VA1277: Polybase network encryption should be enabled

PolyBase is a technology that accesses and combines both non-relational and relational data, all from within SQL Server. Polybase network encryption option configures SQL Server to encrypt control and data channels when using Polybase. This rule verifies that this option is enabled.

Rationale:

Having any communication protocol without encryption can lead to multiple security problems, including data loss, data tampering & leak of authentication credentials.

Query:

SELECT CASE 
         WHEN EXISTS (SELECT * 
                      FROM   sys.configurations 
                      WHERE  NAME = 'polybase network encryption' 
                             AND Cast(value AS INT) = 0) THEN 1 
         ELSE 0 
       END AS Violation

Remediation:

Enable polybase network encryption (default)

EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE;
EXECUTE sp_configure 'ad hoc distributed queries', 1; RECONFIGURE;
EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE;

☝ back to top


Severity: Medium

VA1044: Remote Admin Connections should be disabled

SQL Server provides a dedicated administrator connection (DAC). The DAC lets an administrator access a running server to execute diagnostic functions or Transact-SQL statements, or to troubleshoot problems on the server. This rule checks that remote dedicated admin connections are disabled.

Rationale:

The Dedicated Admin Connection (DAC) is intended to be used by administrators for troubleshooting in scenarios when normal connections are not available due to an abnormal state of the system. For scenarios, other than clusters, the DAC is intended to be used only on the same node, and not remotely, to prevent automated attacks against this entry point.

Query:

SELECT CASE 
         WHEN EXISTS (SELECT * 
                      FROM   sys.configurations 
                      WHERE  NAME = 'remote admin connections' 
                             AND Cast(value AS INT) = 1) THEN 1 
         ELSE 0 
       END AS Violation

Remediation:

Disable remote dedicated admin connections.

EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE;
EXECUTE sp_configure 'remote admin connections', 0; RECONFIGURE;
EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE;

VA1051: AUTO_CLOSE should be disabled on all databases

The AUTO_CLOSE option specifies whether the database shuts down cleanly and frees resources after the last user exits. This rule checks that this option is disabled on all databases.

Rationale:

Databases marked with AUTO_CLOSE allows the DB to be closed if there are no active connections. In the case of partially contained databases, the authentication of users occurs within the database itself, so the database must be opened every time to authenticate a user. Frequent opening/closing of the database consumes additional resources and may contribute to a denial of service attack.

Query:

SELECT CASE 
         WHEN EXISTS (SELECT * 
                      FROM   sys.databases 
                      WHERE  NAME = Db_name() 
                             AND is_auto_close_on = 1) THEN 1 
         ELSE 0 
       END       AS Violation, 
       Db_name() AS [Database]

Remediation:

Disable the AUTO_CLOSE option on the affected databases.

ALTER DATABASE [$1] SET AUTO_CLOSE OFF

VA1143: ‘dbo’ user should not be used for normal service operation

The ‘dbo’, or database owner, is a user account that has implied permissions to perform all activities in the database. Members of the sysadmin fixed server role are automatically mapped to dbo. This rule checks that dbo is not the only account allowed to access this database. Please note that on a newly created clean database this rule will fail until additional roles are created.

Rationale:

A compromised service that accesses the database with the ‘dbo’ user account will have full control of the database. To avoid this situation, lower privileged users should be defined for normal service operation, while the ‘dbo’ account should only be used for administrative tasks that require this privilege.

Query:

IF((SELECT count(*) from sys.database_principals  WHERE principal_id >= 5 AND principal_id  0) SELECT 0 AS Violation
ELSE SELECT 1 AS Violation

Remediation:

Create users with low privileges to access the DB and any data stored in it with the appropriate set of permissions.

VA1244: Orphaned users should be removed from SQL server databases

A database user that exists on a database, but has no corresponding login in master database or as an external resource (i.e. Windows user) is referred to as an orphaned user and it should either be removed or remapped to a valid login. This rule checks that there are no orphaned users.

Rationale:

Orphaned users are typically signs of a misconfiguration. These users create a risk because potential attackers might get access to them and inherit their permissions on the database.

Query:

SELECT NAME AS Principal 
FROM sys.database_principals
WHERE sid NOT IN (
        SELECT sid
        FROM sys.server_principals
        )
    AND authentication_type_desc = 'INSTANCE'
    AND type = 'S'
    AND principal_id != 2
    AND DATALENGTH(sid) <= 28
ORDER BY NAME

Remediation:

Drop the orphaned users or remap them to a different login.

DROP USER [$0]

☝ back to top


Wanna know how I generated this page?
I used this script here.

Cover Image by TheDigitalWay from Pixabay

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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