Select any two versions of this STIG to compare the individual requirements
Select any old version/release of this STIG to view the previous requirements
Check the configuration of SQL Server, the operating system and any monitoring/management tools to verify the system activates an alarm and/or triggers a shutdown of SQL Server when a component failure is detected. If system does not take either or both actions, this is a finding.
Configure the system to activate an alarm and/or trigger a SQL Server shutdown when a component failure is detected.
Security-related errors must be identified and monitored. In most cases, these items would appear in the SQL Server log file. If security-related error conditions are not being monitored to meet this requirement, this is a finding.
Monitor SQL Server log files to determine when a security-related error occurs. Add/Update list of appropriate personnel that are to be alerted when a security related error condition occurs to system documentation. Consider an automated job for both the monitor and the alerting.
If the DBMS exists in the unclassified environment, and data transmission does not cross the boundary between the NIPRNet and the wider Internet, and the application owner and authorizing official have determined that encryption is not required, this is not a finding. Check SQL Server and network settings to determine whether cryptographic mechanisms are used to prevent the unauthorized disclosure of information during transmission. If not, this is a finding. Review system documentation to determine whether the system handles classified information. If the system does not handle classified information, the severity of this check should be downgraded to Category II. From Command Prompt, open SQL Server Configuration Manager by typing sqlservermanager11.msc, and pressing [ENTER]. Navigate to SQL Server Configuration Manager >> SQL Server Network Configuration. Right click on Protocols for [NAME OF INSTANCE], where [NAME OF INSTANCE] is a placeholder for the SQL Server instance name, and click on Properties. On the Flags tab, if Force Encryption is set to "YES", examine the certificate used on the Certificate tab. If Force Encryption is set, a DoD Certificate is not utilized, and a physical encryption measure is utilized, examine the physical encryption devices to determine the following: 1. The plaintext connection to the database server is afforded the highest protections, allowing no access to unauthorized or non-cleared personnel. 2. The encryption device is configured to pass traffic to only the specific IP addresses as identified by the database documentation. 3. The encryption keys utilized are current and valid keys. 4. The keys utilized meet approved organizationally defined compliant algorithms. If any of the preceding requirements is not met, this is a finding. If Force Encryption is set to "NO" or a DoD Certificate is not utilized, and physical encryption measures are not utilized, this is a finding.
Deploy organization-approved encryption to the SQL Server network connections. Where physical network devices are used for encryption, set them up such that: 1. The plaintext connection to the database server is afforded the highest protections, allowing no access to unauthorized or non-cleared personnel. 2. The encryption device is configured to pass traffic to only the specific IP addresses as identified by the database documentation. 3. The encryption keys utilized are current and valid keys. 4. The keys utilized meet approved organizationally defined compliant algorithms. Where SQL Server network encryption is used, open SQL Server Configuration Manager. Navigate to SQL Server Configuration Manager >> SQL Server Network Configuration. Right click on Protocols for [NAME OF INSTANCE], where [NAME OF INSTANCE] is a placeholder for the SQL Server instance name, and click on Properties. On the Flags tab, set Force Encryption to YES, provide a DoD certificate on the Certificate tab.
If Database Availability Groups are not being used, this is not applicable (NA). Check the system documentation and check with the administrator regarding processing resources of the backup/secondary SQL Server. If the primary SQL Server has a backup/secondary server that is dedicated 100% to the primary server's processing, this is not a finding. If the secondary/backup SQL Server is already partly resourced to process something other than that of the primary SQL Server processing, then determine what resources would be required for the secondary/backup SQL Server. If the secondary/backup SQL Server is determined to not have enough processing resources to fulfill the function of the primary server's SQL Server process, this is a finding.
Allocate replacement server(s) to provide failover support to the Primary SQL Server. If a single solution cannot be employed, split the processing of a secondary SQL Server amongst two or more secondary servers.
Review system documentation and determine if one type or more of SQL Server users has a business need for priority usage over other types of users. The need for prioritization most frequently occurs when SQL Server resources are shared between two or more applications or systems where the number of users on more than one system is small or non-existent. This needs to be the case, because SQL Server limits resource based on user accounts and not what process is running. If SQL Server has users that are determined to run significantly high priority processes than other users and the SQL Server "Resource Governor" is not being implemented, this is a finding.
SQL Server utilizes the "Resource Governor" to determine who is allowed high processing resources. There are several configurations regarding the "Resource Governor" that mostly comes down to users or groups of users having a "MAX_CPU_PERCENT", "MIN_CPU_PERCENT", "MIN_MEMORY_PERCENT", and/or "MAX_MEMORY_PERCENT" settings. Users are assigned to Workgroups and the Workgroups are configured processing resources via the "Resource Governor".
Determine elements of security functionality (lists of permissions, additional authentication information, stored procedures, application specific auditing, etc.) which are being housed inside SQL server. For any elements found, check SQL Server to determine if these objects or code implementing security functionality are located in a separate security domain, such as a separate database or schema created specifically for security functionality. Run the following queryto list all the user-defined databases: SELECT Name FROM sys.databases WHERE database_id > 4 ORDER BY 1; If security-related database objects or code are not kept separate, this is a finding.
Locate security-related database objects and code in a separate database, schema, or other separate security domain from database objects and code implementing application logic.
Review system documentation to determine if the labeling of sensitive data is required under organization-defined guidelines. If the labeling of sensitive data is not required, this is NA. Obtain system configuration setting to determine how data labeling is being performed. This can be through triggers or some other SQL developed means or via a third-party tool. Check to ensure that labels are being associated to data when information is being exchanged between systems. If the labeling is not being associated to data when exchanging data between systems, this is a finding.
Develop SQL code or acquire a third party tool to perform data labeling. SQL Server Label Security Toolkit can be downloaded from http://www.codeplex.com. This tool can satisfy all data labeling and security data labeling requirements.
If SQL Server is not housing or distributing publicly available information, this finding is NA. Obtain from the DBA or system documentation the list of publicly available data within SQL Server and the role names that assign read-only access to that public data. Obtain the publicly available user account name being used to access SQL Server. Navigate to Start >> Administrative Tools >> Server Manager >> Server Manager (<'server name'>) >> Configuration >> Local Users and Groups >> Groups >> right click 'Guests' >> Properties >> 'Members:' The publicly available user account will likely be in the OS 'Guests' group. Determine if SQL Server is granting more than read access to the publicly available information through SQL Server 'Server Roles'. Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> right click <'user account'> >> Properties >> Server Roles. If any 'Server Roles' are marked that grant more than read access to the publicly available information, this is a finding.
Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> right click <'user account'> >> Properties >> Server Roles. Uncheck the 'Server Roles' that are checked and grant more than read-only access to the publicly available information.
If SQL Server is not housing or distributing publicly available information, this finding is NA. Obtain from the DBA or system documentation the list of publicly available data within SQL Server. Obtain the publicly available user account name being used to access SQL Server. Navigate to Start >> Administrative Tools >> Server Manager >> Server Manager (<'server name'>) >> Configuration >> Local Users and Groups >> Groups >> right click 'Guests' >> Properties >> 'Members:' The publicly available user account will likely be in the OS 'Guests' group. Determine if SQL Server is granting more than read access to the publicly available information through SQL Server 'User Mapping'. Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> right click <'user account'> >> Properties >> User Mapping. If any of the three system databases are checked (indicating a granted privilege): master, model, or msdb, this is a finding.
Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> right click <'user account'> >> Properties >> User Mapping >> highlight checked database. Uncheck the 'Database role membership' that is checked and grants more than read-only access to the publicly available information.
If SQL Server is not housing or distributing publicly available information, this finding is NA. If SQL Server supports an application collecting information from the public, this is NA. Obtain the publicly available user account name being used to access SQL Server. Using an account with System Administrator privileges, from a command prompt, type lusrmgr.msc, and press [ENTER]. Navigate to Groups >> right click 'Guests' >> Properties >> 'Members:' The publicly available user account will be in the OS 'Guests' group, or another explicitly defined group. Determine if the obtained publicly available user account is located in any other groups. In lusrmgr.msc, navigate to Users. Right click publicly available account name. Click Properties, then click the 'Member of' tab. If the publicly available user account is found in any group 'Members' listing other than 'Guests', this is a finding. In SQL, for the account that is used for public access, ensure that read-only access is the only access granted. If any other access is granted, this is a finding.
Using an account with System Administrator privileges, from a command prompt, type lusrmgr.msc, and press [ENTER]. Navigate to Groups. Locate the additional group(s) from which the publicly available user account must be removed. Right click <'the group to modify' >> Properties >> 'Members:' Remove the publicly available user account from the group by clicking/highlighting the account and then clicking the 'Remove' button. Revoke any update permissions for a guest being used in the context of a guest account.
If SQL Server is not housing or distributing publicly available information, this finding is NA. If SQL Server supports an application collecting information from the public, this is NA. Obtain from the DBA or system documentation the list of publicly available data within SQL Server. Obtain the publicly available user account(s) being used to access SQL Server. Determine if SQL Server is granting more than read access to the publicly available information through SQL Server 'Securables'. Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> right click <'user account'> >> Properties >> Securables. If any 'Securables' are listed, this is a finding.
Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> right click <'user account'> >> Properties >> Securables >> highlight 'Securable Name'. Uncheck all 'Grant', 'With Grant', and 'Deny' for the highlighted 'Securable'.
If the system exists in the non-classified environment, this is NA. For each database under the SQL Server instance, review the system documentation to determine whether the database holds classified or sensitive information. If it does not, this is not a finding. If it does handle classified or sensitive information, review the system documentation and configuration to determine whether the classified information is protected by NSA- and NIST-approved cryptography. If not, this is a finding. If DBMS data encryption is required, ensure the status of encryption by executing: SELECT d.name AS [Database Name], CASE e.encryption_state WHEN 0 THEN 'No database encryption key present, no encryption' WHEN 1 THEN 'Unencrypted' WHEN 2 THEN 'Encryption in progress' WHEN 3 THEN 'Encrypted' WHEN 4 THEN 'Key change in progress' WHEN 5 THEN 'Decryption in progress' WHEN 6 THEN 'Protection change in progress' END AS [Encryption State] FROM sys.dm_database_encryption_keys e RIGHT JOIN sys.databases d ON DB_NAME(e.database_id) = d.name WHERE d.name NOT IN ('master','model','msdb') ORDER BY 1 ; For each user database where encryption is required, verify that encryption is in effect. If not, this is a finding.
Configure SQL Server to encrypt sensitive or classified data stored in each database. Use only NIST-certified or NSA-approved cryptography to provide encryption.
Review system documentation to determine whether cryptography for classified or sensitive information is required by the information owner. If the system documentation does not specify the type of information hosted on SQL Server: classified, sensitive and/or unclassified, this is a finding. If neither classified nor sensitive information exists within SQL Server databases or configuration, this requirement is NA. Note: If the SQL Server is compliant, nothing is displayed. If cryptography is being used by SQL Server, examine evidence that an audit record is created whenever the asymmetric key is accessed by other than authorized users. In particular, view evidence that access by a SYSADMIN or other system privileged account results in the generation of an audit record. This is required because system privileges allow access to encryption keys and can be used to access sensitive data where there is not a need-to-know. Note: The list of acceptable algorithms: "AES 128", "AES 192", "AES 256" and "Triple DES". If cryptography is being used by SQL Server, verify that the cryptography is NIST FIPS 140-2 certified by running the following SQL query: EXEC sp_MSforeachdb ' DECLARE @nCount integer; SELECT @nCount = Count(*) FROM [?].sys.symmetric_keys WHERE key_algorithm NOT IN (''D3'',''A1'',''A2'',''A3''); IF @nCount > 0 SELECT ''?'' AS ''database ?'' , name , algorithm_desc FROM [?].sys.symmetric_keys WHERE key_algorithm NOT IN (''D3'',''A1'',''A2'',''A3'') ORDER BY name, algorithm_desc; ' ; If any items list showing an uncertified NIST FIPS 140-2 algorithm type, this is a finding. If an audit record is not generated for unauthorized access to the asymmetric key, this is a finding. Detailed information on the NIST Cryptographic Module Validation Program (CMVP) is available at the following website: http://csrc.nist.gov/groups/STM/cmvp/index.html.
Document within the system documentation the type of information hosted on SQL Server: classified, sensitive, and/or unclassified. Obtain and utilize native or third-party NIST-validated FIPS 140-2 compliant cryptography solution on SQL Server. Configure cryptographic functions to use FIPS 140-2 compliant algorithms functions. Use DoD certificates to create asymmetric keys stored in the database and used to encrypt sensitive data stored in the database. Run the following SQL script to create a certificate: USE <database name> CREATE CERTIFICATE <certificate name> ENCRYPTION BY PASSWORD = '<password>' FROM FILE = '<path/file_name>' WITH SUBJECT = '<name of person creating key>', EXPIRY_DATE = '<expiration date: yyyymmdd>' Run the following SQL script to create a symmetric key and assign an existing certificate: USE <database name> CREATE SYMMETRIC KEY <'key name'> WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE <certificate name> Assign the application object owner account as the owner of asymmetric and symmetric keys, and certificates. (Ownership is assigned via the AUTHORIZATION clause of the CREATE statement, or the ALTER AUTHORIZATION statement.) Create audit events for access to the key by other than the application owner account or approved application objects. (If using a server-level SQL Server Audit specification, DATABASE_OBJECT_PERMISSION_CHANGE_GROUP accomplishes this.) Revoke any privileges on encryption keys assigned to principals other than the application object owner account and authorized users. Protect the private key by encrypting it with the database or service master key. For whole-database encryption (Transparent Data Encryption - TDE): USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>'; CREATE CERTIFICATE <certificate name> . . .; USE <database name>; CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE <certificate name>; ALTER DATABASE <database name> SET ENCRYPTION ON;
Review documentation, SQL Server settings and authentication system settings to determine if non-organizational users are individually identified and authenticated when logging onto the system. If non-organizational users are not uniquely identified and authenticated, this is a finding.
Configure SQL Server to uniquely identify and authenticate all non-organizational users who log onto the system. This likely would be done via a combination of the operating system with unique accounts and the SQL Server by ensuring mapping to individual accounts.
Since Windows security is being leveraged, this check applies to database configuration files, associated scripts, and applications external to SQL Server that access the database. Ask the DBA and/or IAO to determine if any SQL Server database objects, database configuration files, associated scripts, or applications defined as external to SQL Server that access the database/user environment files/settings contain database passwords. If any do, confirm that SQL Server passwords stored externally to the SQL Server are encoded or encrypted. If any passwords are stored in clear text, this is a finding.
Develop, document, and maintain a list of SQL Server database objects, database configuration files, associated scripts, and applications defined within or external to SQL Server that access the database/user environment files/settings in the System Security Plan. Record whether they do or do not contain SQL Server passwords. If passwords are present, ensure they are encrypted.
Review SQL Server users to determine whether shared accounts exist. If accounts are determined to be shared, determine if individuals are first individually authenticated. If individuals are not individually authenticated before using the shared account (e.g., by the operating system or possibly by an application making calls to the database), this is a finding. If accounts are determined to be shared, determine if they are directly accessible to end users. If so, this is a finding.
Remove user-accessible shared accounts and use individual userids. Build/configure applications to ensure successful individual authentication prior to shared account access. Ensure each user's identity is received and used in audit data in all relevant circumstances.
Review SQL Server users to determine whether shared accounts exist. (This does not include when SQL Server has a guest or public account that is providing access to publicly available information.) If accounts are determined to be shared, determine if individuals are first individually authenticated. If individuals are not individually authenticated before using the shared account (e.g., by the operating system or possibly by an application making calls to the database), this is a finding. If accounts are determined to be shared, determine if they are directly accessible to end users. If so, this is a finding.
Remove user-accessible shared accounts and use individual userids. Build/configure applications to ensure successful individual authentication prior to shared account access. Ensure each user's identity is received and used in audit data in all relevant circumstances.
Review evidence of inclusion of SQL Server software libraries in current backup records. If the backup tool does not include SQL Server, this is a finding.
Ensure inclusion of all SQL Server software libraries into the backup process.
Windows Server Backup, or a 3rd Party Backup Tool, can be utilized to perform this function. Determine how SQL Server is being backed up. If there is no scheduled backup or if organizationally defined backup policy and procedures does not exist, this is finding. Check evidence of inclusion of system-level information into current backup records, if the organizationally defined backup policy, procedures, and backup configurations is not including system-level information backups, this is a finding.
Deploy a backup solution to perform backups as per organizationally defined Backup Policy.
Obtain authorized access list for backup and restoration procedures from system documentation. If documented procedures are insufficient to show or describe authorized personnel, this is a finding. Review file protections assigned to online backup and restoration files. Review access protections and procedures for offline backup and restoration files. If backup or restoration files are subject to unauthorized access, this is a finding. It may be necessary to review backup and restoration procedures to determine ownership and access during all phases of backup and recovery. In addition to physical and host system protections, consider other methods including encryption protection of the files.
Develop, document, and implement protection against unauthorized access of backup and restoration files. Document personnel and the level of access authorized for each to the backup and restoration files in the system documentation.
Review SQL Server's documented testing and recovery procedures that exist in the system documentation. If the testing or recovery procedures are not documented in the system documentation, this is a finding. If the documented testing or recovery procedures are not sufficient to test or recover SQL Server configuration and databases, this is a finding. Review evidence of implementation of testing and verification procedures by reviewing logs from backup and recovery implementation. Logs may be in electronic form or hardcopy, and may include email or other notification. If the system recovery testing has not been implemented and documented, this is a finding.
Develop or update recovery procedures and add the new recovery procedures to the system documentation. Plan for and test system recovery procedures and document the test.
Review the database backup procedures and implementation evidence. Evidence of implementation includes records of backup events and physical review of backup media. Evidence should match the backup plan as recorded in the system documentation. If backup procedures do not exist or are not implemented in accordance with the procedures, this is a finding.
Develop, document, and implement database backup procedures.
Windows Server Backup, or a 3rd Party Backup Tool, can be utilized to perform this function. Determine how SQL Server is being backed up. If there is no scheduled backup or if organizationally defined backup policy and procedures does not exist, this is finding. Check evidence of inclusion user-level information into current backup records, if the organizationally defined backup policy, procedures, and backup configurations is not including user-level information backups, this is a finding.
Deploy a backup solution to perform backups as per organizationally defined Backup Policy.
Obtain the SQL Server recovery procedures and technical system features to determine if mechanisms exist and are in place to specify use of trusted files during SQL Server recovery. If recovery procedures do not exist or are not sufficient to ensure recovery is done in a secure and verifiable manner, this is a finding. Check the configurations of all transaction log files that are enabled by running the following SQL Server query: EXEC sp_MSforeachdb ' SELECT ''?'' AS ''database name'' , name AS ''log file name'' , physical_name AS ''log file location and name'' , state_desc , size , max_size , growth , is_percent_growth FROM [?].sys.database_files WHERE type_desc = ''LOG'' AND state = 0; ' ; If any transaction log files are not configured correctly for size, max_size, and growth to log sufficient transaction information, this is a finding.
Implement SQL Server recovery procedures to ensure the use of trusted files during SQL Server recovery. Modify the parameters for the transaction log file(s) for the system databases: Navigate to SQL Server Management Studio >> Object Explorer >> <SQL Server instance name> >> Databases >> System Databases >> right-click on <system database name> >> Properties >> Files. OR Modify the parameters for the transaction log file(s) for application databases: Navigate to SQL Server Management Studio >> Object Explorer >> <SQL Server instance name> >> Databases >> right-click on <user-defined database name> >> Properties >> Files. THEN Define additional space for the transaction log file, or extra transaction log files, as necessary. To modify Initial Size (MB), click in the "Initial Size (MB)" field for the log file in question, then edit the value. To modify Autogrowth, click on the "Autogrowth/Maxsize" button for the log file in question, choose "In Percent" or "In Megabytes", enter value, and then click OK. To modify Maximum File Size, click on the "Autogrowth/Maxsize" button for the log file in question, choose "Limited to (MB)", enter value, and then click OK. Do not select "Unlimited".
Review the SQL Server configuration and settings for functions, ports, protocols, and services that are not approved or are not used, but are available. To determine the protocol(s) enabled for SQL Server, open SQL Server Configuration Manager. In the left-hand pane, expand SQL Server Network Configuration. Click on the entry for the SQL Server instance under review: "Protocols for <instance name>". The right-hand pane displays the protocols enabled for the instance. To determine whether SQL Server is configured to use a fixed port or dynamic ports, in the right-hand pane double-click on the TCP/IP entry, to open the Properties dialog. (The default fixed port is 1433.) To see which ports are open on the server, run netstat-a from a Windows command prompt. If any ports, protocols, and/or services that are not approved or are not used, are available, this is a finding.
Disable functions, ports, protocols, and services that are not approved or are not used, but are enabled.
Review the list of user-defined Stored Procedures and Functions by running the following SQL query: EXEC sp_MSforeachdb ' DECLARE @nCount integer; SELECT @nCount = Count(*) FROM [?].sys.objects WHERE type in (''FN'', ''P'') AND is_ms_shipped <> 1; IF @nCount > 0 SELECT ''?'' AS ''Table Name'', * FROM [?].sys.objects WHERE type in (''FN'', ''P'') AND is_ms_shipped <> 1; ' ; If any user-defined Stored Procedures and Functions are unauthorized and therefore should be prohibited or restricted and are not, this is a finding.
To remove a function from SQL Server, run the following SQL Script: DROP FUNCTION <'function name'> To remove a Stored Procedure from SQL Server, run the following SQL Script: DROP PROCEDURE <'stored procedure name'> If the user-defined Stored Procedures and Functions need to remain available, but access needs to be more restricted, then the user-defined Stored Procedures and Functions should be moved to a separate schema or database that has more restrictive access.
To determine if xp_cmdshell is enabled, execute the following commands: EXEC SP_CONFIGURE 'show advanced option', '1'; RECONFIGURE WITH OVERRIDE; EXEC SP_CONFIGURE 'xp_cmdshell'; If the value of config_value is 1, this is a finding.
To disable the use of xp_cmdshell, from the query prompt: EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO EXEC sp_configure 'xp_cmdshell', 0 GO RECONFIGURE GO
Check SQL Server settings to determine if the 'sa' (sysadmin) account has been disabled by executing the following query: USE MASTER GO SELECT name, is_disabled FROM sys.sql_logins WHERE principal_id = 1; Verify that the "name" column contains the current name of the sa database server account (see note). If the "is_disabled" column is not set to 1, this is a finding. Note: If the 'sa' account name has been changed per SQL2-00-010200, its new name should appear in the query results.
Modify the enabled flag of SQL Server's "sa" (sysadmin) account by running the following script. If the account name has been changed per SQL2-00-010200, replace the letters "sa" in the query with the new name. USE master; GO ALTER LOGIN [sa] DISABLE;
Review the components and features included in SQL Server and capable of being disabled (by configuration settings, permissions and privileges, etc.). Take note of those which are enabled. Review the system documentation to verify that the enabled components or features are documented and authorized. If any enabled components or features are not authorized, this is a finding.
If any components or features of SQL Server are required for operation of applications that will be accessing SQL Server data or configuration, include them in the system documentation. If any unused components or features of SQL Server are installed and cannot be uninstalled or removed, then disable those components or features.
If the SQL Server service "SQL Server Analysis Services (MSSQLSERVER)" is used and the service satisfies functional organizational requirement, this is not a finding. If there is no functional organizational requirement for the "SQL Server Analysis Services (MSSQLSERVER)" service make sure that the service is not installed or is disabled. From command prompt, using an account with System Administrator Privilege, open dcomcnfg. Navigate to Console Root >> Services (Local) >> [sort by name] >> locate: "SQL Server Analysis Services (MSSQLSERVER)". If the "SQL Server Analysis Services (MSSQLSERVER)" service does not exist, this is not a finding. If the "SQL Server Analysis Services (MSSQLSERVER)" status is "Started" or the "Startup Type" is not "Disabled", this is a finding.
Remove the SQL Server Analysis Service (SSAS) software component.
If the SQL Server service "SQL Server Integration Services 11.0" is used and the service satisfies functional organizational requirement, this is not a finding. If there is no functional organizational requirement for the "SQL Server Integration Services 11.0" service make sure that the service is not installed or is disabled. From command prompt, using an account with System Administrator Privilege, open dcomcnfg. Navigate to Console Root >> Services (Local) >> [sort by name] >> locate: "SQL Server Integration Services 11.0". If the "SQL Server Integration Services 11.0" service does not exist, this is not a finding. If the "SQL Server Integration Services 11.0" status is "Started" or the "Startup Type" is not "Disabled", this is a finding.
Remove the SQL Server Integrated Services (SSIS) software component.
If there is no functional organizational requirement for the "SQL Server Reporting Services (MSSQLSERVER)" service, make sure that the service is not installed or that the service is disabled. If the SQL Server service "SQL Server Reporting Services (MSSQLSERVER)" is used and the service satisfies functional organizational requirement, this is not a finding. From command prompt, using an account with System Administrator Privilege, open dcomcnfg. Navigate to Console Root >> Services (Local) >> [sort by name] >> locate: "SQL Server Reporting Services (MSSQLSERVER)". If the "SQL Server Reporting Services (MSSQLSERVER)" service does not exist, this is not a finding. If the "SQL Server Reporting Services (MSSQLSERVER)" status is "Started" or the "Startup Type" is not set to "Disabled", this is a finding.
Remove the SSRS from SQL Server.
Review the list of components and features installed with the database. Using an account with System Administrator privileges, from Command Prompt, open control.exe. Navigate to Programs and Features. Check for the following entries in the 'Uninstall or change a program' window. Microsoft SQL Server Data Tools - Database Projects - Web installer entry point Prerequisites for SSDT If SQL Server Data Tools is not documented as a server requirement, and these entries exist, this is a finding.
Document the requirement for SQL Server Data Tools to reside on this server. If it is not required, using an account with System Administrator privileges, from Command Prompt, open control.exe. Navigate to Programs and Features. Remove the following entries in the 'Uninstall or change a program' window. Microsoft SQL Server Data Tools - Database Projects - Web installer entry point Prerequisites for SSDT
Check SQL Server for the existence of the publicly available "AdventureWorks" database by performing the following query: SELECT name from sysdatabases where name like 'AdventureWorks%'; If the "AdventureWorks" database is present, this is a finding.
Remove the publicly available "AdventureWorks" database from SQL Server by running the following query: DROP DATABASE AdventureWorks
Check SQL Server for the existence of the publicly available "NorthWind" database by performing the following query: SELECT name from sysdatabases where name like 'Northwind%'; If the "Northwind" database is present, this is a finding.
Remove the publicly available "Northwind" database from SQL Server by running the following query: DROP DATABASE Northwind
Obtain the SQL Server software directory location: from a command prompt, open the registry editor by typing regedit.exe and pressing [ENTER]. Navigate to the following registry location: HKEY_LOCAL_MACHINE >> SOFTWARE >> Microsoft >> Microsoft SQL Server >> [INSTANCE NAME] >> Setup >> SQLBinRoot In the registry tree, the [INSTANCE NAME] for a SQL Server 2012 database engine instance is normally shown as "MSSQL11" followed by a period and the name that was specified for the SQL Server service at installation time. If multiple SQL Server instances are installed, each will have its own [INSTANCE NAME] node and subtree in the registry. The value in the Data column for the SQLBinRoot registry entry is the file system path for the SQL Server 2012 binaries. Navigate to that folder location using a command prompt or Windows Explorer. The following instructions assume that Windows Explorer is used. Verify that files and folders that are part of the SQL Server 2012 instance have only authorized privileges. Right-click the binaries (\binn) folder, click Properties. On the Security tab, verify that at most the following permissions are present: Trusted Installer (Full Control) CREATOR OWNER (Full Control) SYSTEM (Full Control) Administrators (Full Control) [See Note 3] Users (Read, List Folder Contents, Read & Execute) Creator Owner (Special Permissions - Full control - Subfolders and files only) All Application Packages (Read & Execute) [Only as needed - see Note 4] SQL Server Service SID OR Service Account (Read & Execute) [Notes 1, 2] SQL Server SQL Agent Service SID OR Service Account, if SQL Server Agent is in use. (Full Control) [Notes 1, 2] SQL Server FD Launcher Service SID OR Service Account, if full-text indexing is in use. (Read & Execute) [Notes 1, 2] System Administrators (Full Control) [Note 3] If any less restrictive permissions are present (and not specifically justified and approved), this is a finding. Right-click each folder under the binaries folder; click Properties. On the Security tab, verify that at most the permissions listed in the preceding paragraph are present. If any less restrictive permissions are present (and not specifically justified and approved), this is a finding. Right-click the \Install folder, which is a peer of \binn, under ...\MSSQL. On the Security tab, verify that at most the permissions listed in the preceding paragraphs are present. If any less restrictive permissions are present (and not specifically justified and approved), this is a finding. Locate the ...\Microsoft SQL Server\110\Shared folder, either by stepping up the tree in Windows Explorer or by finding the file path in the registry at: HKEY_LOCAL_MACHINE >> SOFTWARE >> Microsoft >> Microsoft SQL Server >> 110 >> SharedCode Right-click on the ...\110\Shared folder; click Properties. On the Security tab, verify that at most the following permissions are present: Trusted Installer (Full Control) CREATOR OWNER (Full Control) System (Full Control) SQL Server Service SID OR Service Account (Read & Execute) [Notes 1, 2] System Administrators (Full Control) [Note 3] Local Administrators (Read) SQL Server Analysis Services (SSAS) Service SID or Service Account, if SSAS is in use (Read & Execute) [Notes 1, 2] SQL Server SQL Agent Service SID OR Service Account, if SQL Server Agent is in use. (Read, Execute, Write) [Notes 1, 2] SQL Server FD Launcher Service SID OR Service Account, if full-text indexing is in use. (Read, Write) [Notes 1, 2] Users (Read, List Folder Contents, Read & Execute) [MsDtsServer110 (Read & Execute) is also permitted, if SSIS/DTS is in use.] [NT AUTHORITY\NETWORK SERVICE (Read & Execute) may also be required for SQL Server Configuration Manager to operate.] If any less restrictive permissions are present (and not specifically justified and approved), this is a finding. Right-click each folder under the ...\110\Shared folder; click Properties. On the Security tab, verify that at most the permissions listed in the preceding paragraph are present. If any less restrictive permissions are present (and not specifically justified and approved), this is a finding. ----- Note 1: It is highly advisable to use a separate account for each service. When installing SQL Server in single-server mode, you can opt to have these provisioned for you. These automatically generated accounts are referred to as virtual accounts. Each virtual account has an equivalent Service SID, with the same name. The installer also creates an equivalent SQL Server login, also with the same name. Applying folder and file permissions to Service SIDs, rather than to domain accounts or local computer accounts, provides tighter control because these permissions are available only to the specific service when it is running and not in any other context. (However, when using failover clustering, a domain account must be specified at installation, rather than a virtual account.) For more on this topic, see http://msdn.microsoft.com/en-us/library/ms143504(v=sql.110).aspx. Note 2: Tips for adding a service SID/virtual account to a folder's permission list. 1) In Windows Explorer, right-click on the folder and select "Properties." 2) Select the "Security" tab 3) Click "Edit" 4) Click "Add" 5) Click "Locations" 6) Select the computer name 7) Search for the name 7.a) SQL Server Service 7.a.i) Type "NT SERVICE\MSSQL" and click "Check Names". (What you have just typed in is the first 16 characters of the name. At least one character must follow "NT SERVICE\"; you will be presented with a list of all matches. If you have typed in the full, correct name, step 7.a.ii is bypassed.) 7.a.ii) Select the "MSSQL$<instance name>" user and click "OK" 7.b) SQL Agent Service 7.b.i) Type "NT SERVICE\SQL" and click "Check Names" 7.b.ii) Select the "SQLAgent$<instance name>" user and click "OK" 8) Click "OK" 9) Permission like a normal user from here Note 3: In the interest of separation of responsibilities with least privilege, consider granting Full Control only to SQL Database Administrators (create a custom group for these) and providing the local Administrators group with Read access only. Note 4: Some files also require 'ALL APPLICATION PACKAGES (READ, EXECUTE)' permissions for certain functionality to work appropriately, and this is considered acceptable where those permissions are required. (All SQL Server files that require this access reside by default in the ..\Microsoft SQL Server\110\ directory.)
Navigate to the SQL Server software directory (\binn) location. Right-click the folder, click Properties. On the Security tab, modify the security permissions, so that files and folders that are part of the SQL Server 2012 installation have at most the following privileges. Right-click each folder under the installation folder, click Properties. On the Security tab, modify the security permissions, so that at most the following permissions are present: Trusted Installer (Full Control) CREATOR OWNER (Full Control) SYSTEM (Full Control) Administrators (Full Control) [See Note 3] Users (Read, List Folder Contents, Read & Execute) Creator Owner (Special Permissions - Full control - Subfolders and files only) All Application Packages (Read & Execute) [Only as needed - see Note 4] SQL Server Service SID OR Service Account (Read & Execute) [Notes 1, 2] SQL Server SQL Agent Service SID OR Service Account, if SQL Server Agent is in use. (Full Control) [Notes 1, 2] SQL Server FD Launcher Service SID OR Service Account, if full-text indexing is in use. (Read & Execute) [Notes 1, 2] System Administrators (Full Control) [Note 3] Repeat the above for the \Install folder. Navigate to the ...\Microsoft SQL Server\110\Shared folder. On the Security tab, modify the security permissions, so that at most the following permissions are present: Trusted Installer (Full Control) CREATOR OWNER (Full Control) System (Full Control) SQL Server Service SID OR Service Account (Read & Execute) [Notes 1, 2] System Administrators (Full Control) [Note 3] Local Administrators (Read) SQL Server Analysis Services (SSAS) Service SID or Service Account, if SSAS is in use (Read & Execute) [Notes 1, 2] SQL Server SQL Agent Service SID OR Service Account, if SQL Server Agent is in use. (Read, Execute, Write) [Notes 1, 2] SQL Server FD Launcher Service SID OR Service Account, if full-text indexing is in use. (Read, Write) [Notes 1, 2] Users (Read, List Folder Contents, Read & Execute) [MsDtsServer110 (Read & Execute) is also permitted, if SSIS/DTS is in use.] [NT AUTHORITY\NETWORK SERVICE (Read & Execute) may also be required for SQL Server Configuration Manager to operate.] ----- Note 1: It is highly advisable to use a separate account for each service. When installing SQL Server in single-server mode, you can opt to have these provisioned for you. These automatically generated accounts are referred to as virtual accounts. Each virtual account has an equivalent Service SID, with the same name. The installer also creates an equivalent SQL Server login, also with the same name. Applying folder and file permissions to Service SIDs, rather than to domain accounts or local computer accounts, provides tighter control because these permissions are available only to the specific service when it is running and not in any other context. (However, when using failover clustering, a domain account must be specified at installation, rather than a virtual account.) For more on this topic, see http://msdn.microsoft.com/en-us/library/ms143504(v=sql.110).aspx. Note 2: Tips for adding a service SID/virtual account to a folder's permission list. 1) In Windows Explorer, right-click on the folder and select "Properties." 2) Select the "Security" tab 3) Click "Edit" 4) Click "Add" 5) Click "Locations" 6) Select the computer name 7) Search for the name 7.a) SQL Server Service 7.a.i) Type "NT SERVICE\MSSQL" and click "Check Names". (What you have just typed in is the first 16 characters of the name. At least one character must follow "NT SERVICE\"; you will be presented with a list of all matches. If you have typed in the full, correct name, step 7.a.ii is bypassed.) 7.a.ii) Select the "MSSQL$<instance name>" user and click "OK" 7.b) SQL Agent Service 7.b.i) Type "NT SERVICE\SQL" and click "Check Names" 7.b.ii) Select the "SQLAgent$<instance name>" user and click "OK" 8) Click "OK" 9) Permission like a normal user from here Note 3: In the interest of separation of responsibilities with least privilege, consider granting Full Control only to SQL Database Administrators (create a custom group for these) and providing the local Administrators group with Read access only. Note 4: Some files also require 'ALL APPLICATION PACKAGES (READ, EXECUTE)' permissions for certain functionality to work appropriately, and this is considered acceptable where those permissions are required. (All SQL Server files that require this access reside by default in the ..\Microsoft SQL Server\110\ directory.)
Check Microsoft's list of supported SQL Server versions. To locate the correct web page, perform a web search for "Microsoft SQL Server end of support." To be considered supported, Microsoft must report that the version is supported by security patches to known vulnerabilities. Check SQL Server version by running the following command: print @@version If the security patch support for SQL Server cannot be determined or SQL Server version is not shown as supported, this is a finding. If SQL Server does not contain the latest security patches, this is a finding.
Upgrade SQL Server to the Microsoft-supported version. Apply the latest SQL Server patches after evaluation of impact.
Verify the SQL Server installations present on the server. From a Command Prompt, type regedit.exe, and press [ENTER]. Navigate to HKEY_LOCAL_MACHINE >> SOFTWARE >> Microsoft >> Microsoft SQL Server >> Instance Names. Each instance installed on the server possesses a key inside a folder under this registry entry. Analysis Services Instances are registered in the OLAP subfolder. Reporting Services Instances are registered in the RS subfolder. Standard SQL Server Instances are registered in the SQL subfolder. Inside each of these folders, a single key is used to reference an Instance's specific Windows Registry tree. Each key will have its own registry tree at the following registry location: HKEY_LOCAL_MACHINE >> SOFTWARE >> Microsoft >> Microsoft SQL Server >> [INSTANCE NAME]. An [INSTANCE NAME] is listed as the Data component of a key found in one of the above OLAP, RS, or SQL folders. To find the installation location of a particular instance, navigate to the following location in the Windows Registry: HKEY_LOCAL_MACHINE >> SOFTWARE >> Microsoft >> Microsoft SQL Server >> [INSTANCE NAME] >> Setup. Examine the value of the 'SqlProgramDir' key. The value of the 'SqlProgramDir' key is the SQL Server installation directory for that SQL Server Instance. Navigate to that folder location using a Command Prompt or Windows Explorer. Only applications that are required for the functioning and administration, not use, of the SQL Server should be located on the same directory node as the SQL Server software libraries. If any files or subfolders that are not part of the SQL Server installation are in the folder, this is a finding.
Separate database files (software, data) into dedicated directories.
Check system documentation for policy and procedures to restrict use of the SQL Server software installation account. Check OS settings to determine whether users are restricted from accessing SQL Server objects and data they are not authorized to access by checking the local OS user accounts. From a Command Prompt, open lusrmgr.msc. Navigate to Users >> right click individual user >> Properties >> Member Of. If appropriate access controls for all users are not implemented to restrict access to only authorized users and to restrict the access of those users to objects and data they are authorized, this is a finding. Review procedures for controlling and granting access to use of the SQL Server software installation account. If access or use of this account is not restricted to the minimum number of personnel required, or unauthorized access to this account has been granted, this is a finding.
From a Command Prompt, open lusrmgr.msc. Navigate to Users >> right click individual user >> Properties >> Member Of. Configure SQL Server & OS settings and access controls, to restrict user access to objects and data that the user is authorized to view or interact with. Develop, document, and implement procedures to restrict use of the DBMS software installation account.
If a security and data integrity tool is not used for monitoring and alerting files and folders based on cryptographic hashes, this is a finding. If the tool does not verify files/folder locations as listed in the documentation, this is a finding.
Include locations of all files, libraries, scripts, and executables that are part of, or related to, the SQL Server 2012 installation in the documentation. Deploy a security and data integrity tool for monitoring and alerting files and folders based on cryptographic hashes, to verify files/folder locations as listed in the documentation.
Verify within the system documentation that SQL Server is monitoring for security-relevant configuration settings to discover unauthorized changes. This can be done by a third-party tool or a SQL script that does baselining and then comparisons. If the monitoring of security-relevant configuration settings to discover unauthorized changes is not implemented on SQL Server, this is a finding.
Document the monitoring of security-relevant configuration settings to discover unauthorized changes within the system documentation. Document the specific users or types of security personnel that are able to monitor security-relevant configuration settings to discover unauthorized changes. Deploy and implement a third-party tool or some other SQL Server method of monitoring security-relevant configuration settings to discover unauthorized changes.
Verify that Files and Folders that are part of the SQL Server 2012 Installation have auditing enabled. Right click the root folder of the SQL Server installation. Typically, this is at <drive>:\Program Files\Microsoft SQL Server\. Select Properties. Click on the Security tab Click on the Advanced button Click on the Auditing tab If "Everyone" is not listed in the "Name" column, this is a finding. If "This folder, subfolders and files" is not listed in the "Apply To" column, this is a finding. When "Everyone" ... " is listed, select the "Everyone" row and click on the Edit button. If either the Successful or Failed checkbox is not selected for any of the following access types, this is a finding: Traverse folder/execute file List folder/read data Read attributes Read extended attributes Create files/write data Create folders/append data Write attributes Write extended attributes Delete Read permissions
Navigate to Advanced Security Settings by selecting Properties > Security > Advanced > Auditing > Continue. Where "Everyone" is missing from the "Name" column, click the Add button; type "Everyone" in the object names box; click the OK button. The Auditing Entry dialog opens. Where "Everyone" is in the "Name" column, select that row and click on the Edit button. The Auditing Entry dialog opens. In the Auditing Entry dialog, set "Apply onto" to "This folder, subfolders and files". In the Auditing Entry dialog, select both the Successful and the Failed checkbox for each of the following access types, where not already selected: Traverse folder/execute file List folder/read data Read attributes Read extended attributes Create files/write data Create folders/append data Write attributes Write extended attributes Delete Read permissions Click OK, OK, OK, OK to save the new settings and exit the dialog boxes.
Obtain the SQL Server software library installation directory location. From a command prompt, type regedit.exe, and press [ENTER]. Navigate to HKEY_LOCAL_MACHINE >> SOFTWARE >> Microsoft >> Microsoft SQL Server >> Instance Names. Each instance installed on the server possesses a key inside a folder under this registry entry. Analysis Services Instances are registered in the OLAP subfolder. Reporting Services Instances are registered in the RS subfolder. Standard SQL Server Instances are registered in the SQL subfolder. Inside each one of these folders, a single key is used to reference an instance's specific Windows Registry tree. Each key will have its own registry tree at the following registry location: HKEY_LOCAL_MACHINE >> SOFTWARE >> Microsoft >> Microsoft SQL Server >> [INSTANCE NAME]. An [INSTANCE NAME] is listed as the data component of a key found in one of the above OLAP, RS, or SQL folders. To find the installation location of a particular instance, navigate to the following location in the Windows Registry: HKEY_LOCAL_MACHINE >> SOFTWARE >> Microsoft >> Microsoft SQL Server >> [INSTANCE NAME] >> Setup. Examine the value of the 'SqlProgramDir' key. The value of the 'SqlProgramDir' key is the SQL Server installation directory for that SQL Server Instance. Navigate to that folder location using a command prompt or Windows Explorer. Note any custom subdirectories within the SQL Server software library directory. Only applications that are required for the functioning and administration of SQL Server should be located in the same disk directory as the SQL Server software libraries. If any directories or files not installed with the SQL Server software exist within the SQL Server software library directory, this is a finding.
Install SQL Server software using directories separate from the OS and other application software library directories. Relocate any directories or reinstall other application software that currently shares the DBMS software library directory to separate directories. Recommend dedicating a separate partition for the SQL software libraries.
Obtain the SQL Server audit file location(s) by running the following SQL script: SELECT DISTINCT LEFT(path, (LEN(path) - CHARINDEX('\',REVERSE(path)) + 1)) AS "Audit Path" FROM sys.traces UNION SELECT log_file_path AS "Audit Path" FROM sys.server_file_audits For each audit, the path column will give the location of the file. Verify that all audit files have the correct permissions by doing the following for each audit file: Navigate to audit folder location(s) using a command prompt or Windows Explorer. Right-click the file/folder, click Properties. On the Security tab, verify that at most the following permissions are applied: Administrator(read) Users (none) Audit Administrator (Full Control) Auditors group (Read) SQL Server Service SID OR Service Account (Full Control) [Notes 1, 2] SQL Server SQL Agent Service SID OR Service Account, if SQL Server Agent is in use. (Read, Execute, Write) [Notes 1, 2] If any less restrictive permissions are present and not specifically justified and approved in the system security plan, this is a finding. If less restrictive permissions are present and specifically justified and approved in the system security plan, this is not a finding ----- Note 1: It is highly advisable to use a separate account for each service. When installing SQL Server in single-server mode, you can opt to have these provisioned for you. These automatically-generated accounts are referred to as virtual accounts. Each virtual account has an equivalent Service SID, with the same name. The installer also creates an equivalent SQL Server login, also with the same name. Applying folder and file permissions to Service SIDs, rather than to domain accounts or local computer accounts, provides tighter control, because these permissions are available only to the specific service when it is running, and not in any other context. (However, when using failover clustering, a domain account must be specified at installation, rather than a virtual account.) For more on this topic, see http://msdn.microsoft.com/en-us/library/ms143504(v=sql.120).aspx. Note 2: Tips for adding a service SID/virtual account to a folder's permission list. 1) In Windows Explorer, right-click on the folder and select "Properties." 2) Select the "Security" tab 3) Click "Edit" 4) Click "Add" 5) Click "Locations" 6) Select the computer name 7) Search for the name 7.a) SQL Server Service 7.a.i) Type "NT SERVICE\MSSQL" and click "Check Names". (What you have just typed in is the first 16 characters of the name. At least one character must follow "NT SERVICE\"; you will be presented with a list of all matches. If you have typed in the full, correct name, step 7.a.ii is bypassed.) 7.a.ii) Select the "MSSQL$<instance name>" user and click OK 7.b) SQL Agent Service 7.b.i) Type "NT SERVICE\SQL" and click "Check Names" 7.b.ii) Select the "SQLAgent$<instance name>" user and click OK 8) Click OK 9) Permission like a normal user from here
Modify audit file permissions to meet the requirement to protect against unauthorized deletion. Navigate to audit folder location(s) using a command prompt or Windows Explorer. Right-click on the file, click Properties. On the Security tab, modify the security permissions to: Administrator(read) Users (none) Audit Administrator(Full Control) Auditors group (Read) SQL Server Service SID OR Service Account (Full Control) [Notes 1, 2] SQL Server SQL Agent Service SID OR Service Account, if SQL Server Agent is in use. (Read, Execute, Write) [Notes 1, 2] ----- Note 1: It is highly advisable to use a separate account for each service. When installing SQL Server in single-server mode, you can opt to have these provisioned for you. These automatically-generated accounts are referred to as virtual accounts. Each virtual account has an equivalent Service SID, with the same name. The installer also creates an equivalent SQL Server login, also with the same name. Applying folder and file permissions to Service SIDs, rather than to domain accounts or local computer accounts, provides tighter control, because these permissions are available only to the specific service when it is running, and not in any other context. (However, when using failover clustering, a domain account must be specified at installation, rather than a virtual account.) For more on this topic, see http://msdn.microsoft.com/en-us/library/ms143504(v=sql.110).aspx. Note 2: Tips for adding a service SID/virtual account to a folder's permission list. 1) In Windows Explorer, right-click on the folder and select "Properties." 2) Select the "Security" tab 3) Click "Edit" 4) Click "Add" 5) Click "Locations" 6) Select the computer name 7) Search for the name 7.a) SQL Server Service 7.a.i) Type "NT SERVICE\MSSQL" and click "Check Names". (What you have just typed in is the first 16 characters of the name. At least one character must follow "NT SERVICE\"; you will be presented with a list of all matches. If you have typed in the full, correct name, step 7.a.ii is bypassed.) 7.a.ii) Select the "MSSQL$<instance name>" user and click OK 7.b) SQL Agent Service 7.b.i) Type "NT SERVICE\SQL" and click "Check Names" 7.b.ii) Select the "SQLAgent$<instance name>" user and click OK 8) Click OK 9) Permission like a normal user from here
Obtain the SQL Server audit file location(s) by running the following SQL script: SELECT DISTINCT LEFT(path, (LEN(path) - CHARINDEX('\',REVERSE(path)) + 1)) AS "Audit Path" FROM sys.traces UNION SELECT log_file_path AS "Audit Path" FROM sys.server_file_audits For each audit, the Audit Path column will give the location of the file. Verify that all audit files have the correct permissions by doing the following for each audit file: Navigate to audit folder location(s) using a command prompt or Windows Explorer. The following instructions assume Windows Explorer is used. Right-click the file/folder, click Properties. On the Security tab, verify that at most the following permissions are applied: Administrator(read) Users (none) Audit Administrator (Full Control) Auditors group (Read) SQL Server Service SID OR Service Account (Full Control) [Notes 1, 2] SQL Server SQL Agent Service SID OR Service Account, if SQL Server Agent is in use. (Read, Execute, Write) [Notes 1, 2] If any less restrictive permissions are present and not specifically justified and approved in the system security plan, this is a finding. If less restrictive permissions are present and specifically justified and approved in the system security plan, this is not a finding. If Trace is in use, SQL Server creates each trace file with a standard set of permissions, overriding the folder permissions. It grants full control to OWNER RIGHTS, Administrators and <SQL Server Instance name>. Since this is not configurable, this is not a finding.
Edit the system security plan to include justification and authorization for any less restrictive permissions that are present and needed. (An example might be where Auditors need "Read & Execute" rather than "Read" alone.) Modify audit file permissions to meet the requirement to protect against unauthorized modification. Navigate to audit folder location(s) using a command prompt or Windows Explorer. Right-click on the file, click Properties. On the Security tab, modify the security permissions to: Administrator(read) Users (none) Audit Administrator(Full Control) Auditors group (Read) SQL Server Service SID OR Service Account (Full Control) [Notes 1, 2] SQL Server SQL Agent Service SID OR Service Account, if SQL Server Agent is in use. (Read, Execute, Write) [Notes 1, 2] ----- Note 1: It is highly advisable to use a separate account for each service. When installing SQL Server in single-server mode, you can opt to have these provisioned for you. These automatically-generated accounts are referred to as virtual accounts. Each virtual account has an equivalent Service SID, with the same name. The installer also creates an equivalent SQL Server login, also with the same name. Applying folder and file permissions to Service SIDs, rather than to domain accounts or local computer accounts, provides tighter control, because these permissions are available only to the specific service when it is running, and not in any other context. (However, when using failover clustering, a domain account must be specified at installation, rather than a virtual account.) For more on this topic, see http://msdn.microsoft.com/en-us/library/ms143504(v=sql.110).aspx. Note 2: Tips for adding a service SID/virtual account to a folder's permission list. 1) In Windows Explorer, right-click on the folder and select "Properties." 2) Select the "Security" tab 3) Click "Edit" 4) Click "Add" 5) Click "Locations" 6) Select the computer name 7) Search for the name 7.a) SQL Server Service 7.a.i) Type "NT SERVICE\MSSQL" and click "Check Names". (What you have just typed in is the first 16 characters of the name. At least one character must follow "NT SERVICE\"; you will be presented with a list of all matches. If you have typed in the full, correct name, step 7.a.ii is bypassed.) 7.a.ii) Select the "MSSQL$<instance name>" user and click OK 7.b) SQL Agent Service 7.b.i) Type "NT SERVICE\SQL" and click "Check Names" 7.b.ii) Select the "SQLAgent$<instance name>" user and click OK 8) Click OK 9) Permission like a normal user from here
Obtain the SQL Server audit file location(s) by running the following SQL script: SELECT DISTINCT LEFT(path, (LEN(path) - CHARINDEX('\',REVERSE(path)) + 1)) AS "Audit Path" FROM sys.traces UNION SELECT log_file_path AS "Audit Path" FROM sys.server_file_audits For each audit, the path column will give the location of the file. Verify that all audit files have the correct permissions by doing the following for each audit file: Navigate to audit folder location(s) using a command prompt or Windows Explorer. Right-click the file/folder, click Properties. On the Security tab, verify that at most the following permissions are applied: Administrator(read) Users (none) Audit Administrator (Full Control) Auditors group (Read) SQL Server Service SID OR Service Account (Full Control) [Notes 1, 2] SQL Server SQL Agent Service SID OR Service Account, if SQL Server Agent is in use. (Read, Execute, Write) [Notes 1, 2] If any less restrictive permissions are present and not specifically justified and approved in the system security plan, this is a finding. If less restrictive permissions are present and specifically justified and approved in the system security plan, this is not a finding. ----- Note 1: It is highly advisable to use a separate account for each service. When installing SQL Server in single-server mode, you can opt to have these provisioned for you. These automatically-generated accounts are referred to as virtual accounts. Each virtual account has an equivalent Service SID, with the same name. The installer also creates an equivalent SQL Server login, also with the same name. Applying folder and file permissions to Service SIDs, rather than to domain accounts or local computer accounts, provides tighter control, because these permissions are available only to the specific service when it is running, and not in any other context. (However, when using failover clustering, a domain account must be specified at installation, rather than a virtual account.) For more on this topic, see http://msdn.microsoft.com/en-us/library/ms143504(v=sql.120).aspx. Note 2: Tips for adding a service SID/virtual account to a folder's permission list. 1) In Windows Explorer, right-click on the folder and select "Properties." 2) Select the "Security" tab 3) Click "Edit" 4) Click "Add" 5) Click "Locations" 6) Select the computer name 7) Search for the name 7.a) SQL Server Service 7.a.i) Type "NT SERVICE\MSSQL" and click "Check Names". (What you have just typed in is the first 16 characters of the name. At least one character must follow "NT SERVICE\"; you will be presented with a list of all matches. If you have typed in the full, correct name, step 7.a.ii is bypassed.) 7.a.ii) Select the "MSSQL$<instance name>" user and click OK 7.b) SQL Agent Service 7.b.i) Type "NT SERVICE\SQL" and click "Check Names" 7.b.ii) Select the "SQLAgent$<instance name>" user and click OK 8) Click OK 9) Permission like a normal user from here
Edit the system security plan to include justification and authorization for any less restrictive permissions that are present and needed. (An example might be where Auditors need "Read & Execute" rather than "Read" alone.) Modify audit file permissions to meet the requirement to protect against unauthorized access. Navigate to audit folder location(s) using a command prompt or Windows Explorer. Right-click on the file, click Properties. On the Security tab, modify the security permissions to: Administrator(read) Users (none) Audit Administrator(Full Control) Auditors group (Read) SQL Server Service SID OR Service Account (Full Control) [Notes 1, 2] SQL Server SQL Agent Service SID OR Service Account, if SQL Server Agent is in use. (Read, Execute, Write) [Notes 1, 2] ----- Note 1: It is highly advisable to use a separate account for each service. When installing SQL Server in single-server mode, you can opt to have these provisioned for you. These automatically-generated accounts are referred to as virtual accounts. Each virtual account has an equivalent Service SID, with the same name. The installer also creates an equivalent SQL Server login, also with the same name. Applying folder and file permissions to Service SIDs, rather than to domain accounts or local computer accounts, provides tighter control, because these permissions are available only to the specific service when it is running, and not in any other context. (However, when using failover clustering, a domain account must be specified at installation, rather than a virtual account.) For more on this topic, see http://msdn.microsoft.com/en-us/library/ms143504(v=sql.110).aspx. Note 2: Tips for adding a service SID/virtual account to a folder's permission list. 1) In Windows Explorer, right-click on the folder and select "Properties." 2) Select the "Security" tab 3) Click "Edit" 4) Click "Add" 5) Click "Locations" 6) Select the computer name 7) Search for the name 7.a) SQL Server Service 7.a.i) Type "NT SERVICE\MSSQL" and click "Check Names". (What you have just typed in is the first 16 characters of the name. At least one character must follow "NT SERVICE\"; you will be presented with a list of all matches. If you have typed in the full, correct name, step 7.a.ii is bypassed.) 7.a.ii) Select the "MSSQL$<instance name>" user and click OK 7.b) SQL Agent Service 7.b.i) Type "NT SERVICE\SQL" and click "Check Names" 7.b.ii) Select the "SQLAgent$<instance name>" user and click OK 8) Click OK 9) Permission like a normal user from here
Obtain the SQL Server audit file location(s) by running the following SQL script: SELECT DISTINCT LEFT(path, (LEN(path) - CHARINDEX('\',REVERSE(path)) + 1)) AS "Audit Path" FROM sys.traces UNION SELECT log_file_path AS "Audit Path" FROM sys.server_file_audits For each audit, the path column will give the location of the file. Verify that all audit files have the correct permissions by doing the following for each audit file: Navigate to audit folder location(s) using a command prompt or Windows Explorer. Right-click the file/folder, click Properties. On the Security tab, verify that at most the following permissions are applied: Administrator(read) Users (none) Audit Administrator (Full Control) Auditors group (Read) SQL Server Service SID OR Service Account (Full Control) [Notes 1, 2] SQL Server SQL Agent Service SID OR Service Account, if SQL Server Agent is in use. (Read, Execute, Write) [Notes 1, 2] If any less restrictive permissions are present (and not specifically justified and approved), this is a finding.
Modify audit file permissions to meet the requirement to protect against unauthorized access. Navigate to the audit folder location(s) using a command prompt or Windows Explorer. Right-click on the file, click Properties. On the Security tab, modify the security permissions to: Administrator(read) Users (none) Audit Administrator(Full Control) Auditors group (Read) SQL Server Service SID OR Service Account (Full Control) [Notes 1, 2] SQL Server SQL Agent Service SID OR Service Account, if SQL Server Agent is in use. (Read, Execute, Write) [Notes 1, 2] ----- Note 1: It is highly advisable to use a separate account for each service. When installing SQL Server in single-server mode, you can opt to have these provisioned for you. These automatically-generated accounts are referred to as virtual accounts. Each virtual account has an equivalent Service SID, with the same name. The installer also creates an equivalent SQL Server login, also with the same name. Applying folder and file permissions to Service SIDs, rather than to domain accounts or local computer accounts, provides tighter control, because these permissions are available only to the specific service when it is running, and not in any other context. (However, when using failover clustering, a domain account must be specified at installation, rather than a virtual account.) For more on this topic, see http://msdn.microsoft.com/en-us/library/ms143504(v=sql.110).aspx. Note 2: Tips for adding a service SID/virtual account to a folder's permission list. 1) In Windows Explorer, right-click on the folder and select "Properties." 2) Select the "Security" tab 3) Click "Edit" 4) Click "Add" 5) Click "Locations" 6) Select the computer name 7) Search for the name 7.a) SQL Server Service 7.a.i) Type "NT SERVICE\MSSQL" and click "Check Names". (What you have just typed in is the first 16 characters of the name. At least one character must follow "NT SERVICE\"; you will be presented with a list of all matches. If you have typed in the full, correct name, step 7.a.ii is bypassed.) 7.a.ii) Select the "MSSQL$<instance name>" user and click OK 7.b) SQL Agent Service 7.b.i) Type "NT SERVICE\SQL" and click "Check Names" 7.b.ii) Select the "SQLAgent$<instance name>" user and click OK 8) Click OK 9) Permission like a normal user from here
Check to see that all required events are being audited. From the query prompt: SELECT DISTINCT traceid FROM sys.fn_trace_getinfo(0); All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace(s) being used for the auditing requirement. In the following, replace # with a trace ID being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#); The following required event IDs should be listed: 14, 15, 18, 20, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 115, 116, 117, 118, 128, 129, 130, 131, 132, 133, 134, 135, 152, 153, 170, 171, 172, 173, 175, 176, 177, 178. If any of the audit event IDs required above is not listed, this is a finding. Notes: 1. It is acceptable to have the required event IDs spread across multiple traces, provided all of the traces are always active, and the event IDs are grouped in a logical manner. 2. It is acceptable, from an auditing point of view, to include the same event IDs in multiple traces. However, the effect of this redundancy on performance, storage, and the consolidation of audit logs into a central repository, should be taken into account. 3. It is acceptable to trace additional event IDs. This is the minimum list. 4. Once this check is satisfied, the DBA may find it useful to disable or modify the default trace that is set up by the SQL Server installation process. (Note that the Fix does NOT include code to do this.) Use the following query to obtain a list of all event IDs, and their meaning: SELECT * FROM sys.trace_events; 5. Because this check procedure is designed to address multiple requirements/vulnerabilities, it may appear to exceed the needs of some individual requirements. However, it does represent the aggregate of all such requirements. 6. Microsoft has flagged the trace techniques and tools used in this Check and Fix as deprecated. They will be removed at some point after SQL Server 2014. The replacement feature is Extended Events. If Extended Events are in use, and cover all the required audit events listed above, this is not a finding.
Create a trace that meets all auditing requirements. The script provided in the supplemental file, Trace.sql, can be used to do this; edit it as necessary to capture any additional, locally defined events.
From the query prompt: SELECT DISTINCT traceid FROM sys.fn_trace_getinfo(0); All currently defined traces for the SQL Server instance will be listed. If no traces are returned, this is a finding. Determine the trace being used for the auditing requirement. Replace # in the following code with a traceid being used for the auditing requirements. From the query prompt, determine whether the trace options include the value 4, which means SHUTDOWN_ON_ERROR: SELECT CAST(value AS INT) FROM sys.fn_trace_getinfo(#) where property = 1; If the query does not return a value, this is a finding. If a value is returned but is not 4 or 6, this is a finding. (6 represents the combination of values 2 and 4. 2 means TRACE_FILE_ROLLOVER.) NOTE: Microsoft has flagged the trace techniques and tools used in this STIG as deprecated. They will be removed at some point after SQL Server 2014. The replacement feature is Extended Events. If Extended Events are in use and configured to satisfy this requirement, this is not a finding. The following code can be used to check Extended Events settings. /********************************** Check to verify shutdown on failure is set. The following settings are what should be returned: name = <name of audit> on_failure = 1 on_failure_desc = SHUTDOWN SERVER INSTANCE **********************************/ SELECT name, on_failure, on_failure_desc FROM sys.server_audits
If a trace does not exist, create a trace specification that complies with requirements. If a trace exists, but is not set to SHUTDOWN_ON_ERROR, modify the SQL Server audit setting to immediately shutdown the database in the event of an audit failure by setting property 1 to a value of 4 or 6 for the audit. (See the SQL Server Help page for sys.sp_trace_create for implementation details.)
Since SQL Server does not support the monitoring of the available audit log file space, utilize Windows File Server Resource Manager or a third-party application to perform this activity. From a Command Prompt, open fsrm.msc. If fsrm.msc is not installed, the File Server Resource Manager is not installed, File and Folder Quota Management is not enabled. If File Server Resource Manager or a third-party tool capable of sending alert notifications based on audit log store requirements is not installed, this is a finding. If fsrm.msc is installed, expand File Server Resource Manager in the left pane. Expand Quota Management. Select Quotas. If Quotas have not been created for defined Audit Log storage locations that meet organizationally defined requirements, this is a finding. In the center pane, select each quota to determine its Path, Limit, Type, and Description. Right click the appropriate quota or quotas, and click Edit Quota Properties. Examine the Notification thresholds panel. If there are no Notification thresholds applied to this Quota, this is a finding. If a Notification Threshold is applied, and it does not send an email alert, or provide an Event Log entry which is handled by an automated Log Alert reporting application, this is a finding. If a third-party application is utilized to fulfill this requirement, and it is not configured to provide a notification, this is a finding.
From File Server Resource Manager: Choose the From Server Selection, Select a server from the server pool, and select the server from the lower menu. Expand the File and Storage Services Role. Then Expand the File and iSCSI Services subtree. Select File Server Resource Manager. Click Add Features. Return to Add Roles and Features Wizard. Click Next. On the Features Tab, Click Next. Click Install to install and enable the FSRM.msc Microsoft Management Console Snap-in tool. From a Command Prompt, open fsrm.msc. Enable File and Folder Quota Management. Create Quotas for previously identified Audit storage locations based on organizationally defined requirements. Right click the appropriate quota or quotas, and click Edit Quota Properties. From the Notification thresholds pane, create a Notification threshold for this Quota utilizing a generate email alert, or a generated Event Log entry.
Check the SQL Server audit setting on the maximum number of files of the trace used for the auditing requirement. Select * from sys.traces. Determine the audit being used to fulfill the overall auditing requirement. Examine the max_files and max_size parameters. SQL will overwrite the oldest files when the max_files parameter has been exceeded. Care must be taken to ensure that this does not happen, or data will be lost. The amount of space determined for logging by SQL Server is calculated by multiplying the maximum number of files by the maximum file size. If auditing will outgrow the space reserved for logging before being overwritten, this is a finding.
Configure the maximum number of audit log files that are to be generated, staying within the number of logs the system was sized to support. Update the max_files parameter of the audits to ensure the correct number of files is defined.
Check the SQL Server audit setting on the maximum file size of the trace used for the auditing requirement. Select * from sys.traces. Determine the audit being used to fulfill the overall auditing requirement. Examine the max_files and max_size parameters. SQL will overwrite the oldest files when the max_files parameter has been exceeded. Care must be taken to ensure that this does not happen, or data will be lost. The amount of space determined for logging by SQL Server is calculated by multiplying the maximum number of files by the maximum file size. If auditing will outgrow the space reserved for logging before being overwritten, this is a finding.
Configure the maximum file size of each audit log file that is to be generated, staying within the file size the system was sized to support. Modify the audit in question to be placed on drives with adequate space or reconfigure to ensure the audit will not fill the space allocated.
From a Command Prompt, open fsrm.msc. If fsrm.msc is not installed, the File Server Resource Manager is not installed; File and Folder Quota Management is not enabled. If File Server Resource Manager or a third-party tool capable of sending alert notifications based on audit log store requirements is not installed, this is a finding. If fsrm.msc is installed, expand File Server Resource Manager in the left pane. Expand Quota Management. Expand Quotas. If Quotas have not been created for defined Audit Log storage locations, this is a finding.
Use File Server Resource Manager (FSRM.msc) to enable File and Folder Quota Management and create quotas for identified Audit storage locations.
Check to see that all required events are being audited. From the query prompt: SELECT DISTINCT traceid FROM sys.fn_trace_getinfo(0); All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace(s) being used for the auditing requirement. In the following, replace # with a trace ID being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#); The following required event IDs should be listed: 14, 15, 18, 20, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 115, 116, 117, 118, 128, 129, 130, 131, 132, 133, 134, 135, 152, 153, 170, 171, 172, 173, 175, 176, 177, 178. If any of the audit event IDs required above is not listed, this is a finding. Notes: 1. It is acceptable to have the required event IDs spread across multiple traces, provided all of the traces are always active, and the event IDs are grouped in a logical manner. 2. It is acceptable, from an auditing point of view, to include the same event IDs in multiple traces. However, the effect of this redundancy on performance, storage, and the consolidation of audit logs into a central repository, should be taken into account. 3. It is acceptable to trace additional event IDs. This is the minimum list. 4. Once this check is satisfied, the DBA may find it useful to disable or modify the default trace that is set up by the SQL Server installation process. (Note that the Fix does NOT include code to do this.) Use the following query to obtain a list of all event IDs, and their meaning: SELECT * FROM sys.trace_events; 5. Because this check procedure is designed to address multiple requirements/vulnerabilities, it may appear to exceed the needs of some individual requirements. However, it does represent the aggregate of all such requirements. 6. Microsoft has flagged the trace techniques and tools used in this Check and Fix as deprecated. They will be removed at some point after SQL Server 2014. The replacement feature is Extended Events. If Extended Events are in use, and cover all the required audit events listed above, this is not a finding.
Create a trace that meets all auditing requirements. The script provided in the supplemental file, Trace.sql, can be used to do this; edit it as necessary to capture any additional, locally defined events.
Check to see that all required events are being audited. From the query prompt: SELECT DISTINCT traceid FROM sys.fn_trace_getinfo(0); All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace(s) being used for the auditing requirement. In the following, replace # with a trace ID being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#); The following required event IDs should be listed: 14, 15, 18, 20, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 115, 116, 117, 118, 128, 129, 130, 131, 132, 133, 134, 135, 152, 153, 170, 171, 172, 173, 175, 176, 177, 178. If any of the audit event IDs required above is not listed, this is a finding. Notes: 1. It is acceptable to have the required event IDs spread across multiple traces, provided all of the traces are always active, and the event IDs are grouped in a logical manner. 2. It is acceptable, from an auditing point of view, to include the same event IDs in multiple traces. However, the effect of this redundancy on performance, storage, and the consolidation of audit logs into a central repository, should be taken into account. 3. It is acceptable to trace additional event IDs. This is the minimum list. 4. Once this check is satisfied, the DBA may find it useful to disable or modify the default trace that is set up by the SQL Server installation process. (Note that the Fix does NOT include code to do this.) Use the following query to obtain a list of all event IDs, and their meaning: SELECT * FROM sys.trace_events; 5. Because this check procedure is designed to address multiple requirements/vulnerabilities, it may appear to exceed the needs of some individual requirements. However, it does represent the aggregate of all such requirements. 6. Microsoft has flagged the trace techniques and tools used in this Check and Fix as deprecated. They will be removed at some point after SQL Server 2014. The replacement feature is Extended Events. If Extended Events are in use, and cover all the required audit events listed above, this is not a finding.
Create a trace that meets all auditing requirements. The script provided in the supplemental file, Trace.sql, can be used to do this; edit it as necessary to capture any additional, locally defined events.
Check to see that all required events are being audited. From the query prompt: SELECT DISTINCT traceid FROM sys.fn_trace_getinfo(0); All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace(s) being used for the auditing requirement. In the following, replace # with a trace ID being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#); The following required event IDs should be listed: 14, 15, 18, 20, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 115, 116, 117, 118, 128, 129, 130, 131, 132, 133, 134, 135, 152, 153, 170, 171, 172, 173, 175, 176, 177, 178. If any of the audit event IDs required above is not listed, this is a finding. Notes: 1. It is acceptable to have the required event IDs spread across multiple traces, provided all of the traces are always active, and the event IDs are grouped in a logical manner. 2. It is acceptable, from an auditing point of view, to include the same event IDs in multiple traces. However, the effect of this redundancy on performance, storage, and the consolidation of audit logs into a central repository, should be taken into account. 3. It is acceptable to trace additional event IDs. This is the minimum list. 4. Once this check is satisfied, the DBA may find it useful to disable or modify the default trace that is set up by the SQL Server installation process. (Note that the Fix does NOT include code to do this.) Use the following query to obtain a list of all event IDs, and their meaning: SELECT * FROM sys.trace_events; 5. Because this check procedure is designed to address multiple requirements/vulnerabilities, it may appear to exceed the needs of some individual requirements. However, it does represent the aggregate of all such requirements. 6. Microsoft has flagged the trace techniques and tools used in this Check and Fix as deprecated. They will be removed at some point after SQL Server 2014. The replacement feature is Extended Events. If Extended Events are in use, and cover all the required audit events listed above, this is not a finding.
Create a trace that meets all auditing requirements. The script provided in the supplemental file, Trace.sql, can be used to do this; edit it as necessary to capture any additional, locally defined events.
Check to see that all required events are being audited. From the query prompt: SELECT DISTINCT traceid FROM sys.fn_trace_getinfo(0); All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace(s) being used for the auditing requirement. In the following, replace # with a trace ID being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#); The following required event IDs should be listed: 14, 15, 18, 20, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 115, 116, 117, 118, 128, 129, 130, 131, 132, 133, 134, 135, 152, 153, 170, 171, 172, 173, 175, 176, 177, 178. If any of the audit event IDs required above is not listed, this is a finding. Notes: 1. It is acceptable to have the required event IDs spread across multiple traces, provided all of the traces are always active, and the event IDs are grouped in a logical manner. 2. It is acceptable, from an auditing point of view, to include the same event IDs in multiple traces. However, the effect of this redundancy on performance, storage, and the consolidation of audit logs into a central repository, should be taken into account. 3. It is acceptable to trace additional event IDs. This is the minimum list. 4. Once this check is satisfied, the DBA may find it useful to disable or modify the default trace that is set up by the SQL Server installation process. (Note that the Fix does NOT include code to do this.) Use the following query to obtain a list of all event IDs, and their meaning: SELECT * FROM sys.trace_events; 5. Because this check procedure is designed to address multiple requirements/vulnerabilities, it may appear to exceed the needs of some individual requirements. However, it does represent the aggregate of all such requirements. 6. Microsoft has flagged the trace techniques and tools used in this Check and Fix as deprecated. They will be removed at some point after SQL Server 2014. The replacement feature is Extended Events. If Extended Events are in use, and cover all the required audit events listed above, this is not a finding.
Create a trace that meets all auditing requirements. The script provided in the supplemental file, Trace.sql, can be used to do this; edit it as necessary to capture any additional, locally defined events.
Check to see that all required events are being audited. From the query prompt: SELECT DISTINCT traceid FROM sys.fn_trace_getinfo(0); All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace(s) being used for the auditing requirement. In the following, replace # with a trace ID being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#); The following required event IDs should be listed: 14, 15, 18, 20, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 115, 116, 117, 118, 128, 129, 130, 131, 132, 133, 134, 135, 152, 153, 170, 171, 172, 173, 175, 176, 177, 178. If any of the audit event IDs required above is not listed, this is a finding. Notes: 1. It is acceptable to have the required event IDs spread across multiple traces, provided all of the traces are always active, and the event IDs are grouped in a logical manner. 2. It is acceptable, from an auditing point of view, to include the same event IDs in multiple traces. However, the effect of this redundancy on performance, storage, and the consolidation of audit logs into a central repository, should be taken into account. 3. It is acceptable to trace additional event IDs. This is the minimum list. 4. Once this check is satisfied, the DBA may find it useful to disable or modify the default trace that is set up by the SQL Server installation process. (Note that the Fix does NOT include code to do this.) Use the following query to obtain a list of all event IDs, and their meaning: SELECT * FROM sys.trace_events; 5. Because this check procedure is designed to address multiple requirements/vulnerabilities, it may appear to exceed the needs of some individual requirements. However, it does represent the aggregate of all such requirements. 6. Microsoft has flagged the trace techniques and tools used in this Check and Fix as deprecated. They will be removed at some point after SQL Server 2014. The replacement feature is Extended Events. If Extended Events are in use, and cover all the required audit events listed above, this is not a finding.
Create a trace that meets all auditing requirements. The script provided in the supplemental file, Trace.sql, can be used to do this; edit it as necessary to capture any additional, locally defined events.
Check to see that all required events are being audited. From the query prompt: SELECT DISTINCT traceid FROM sys.fn_trace_getinfo(0); All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace(s) being used for the auditing requirement. In the following, replace # with a trace ID being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#); The following required event IDs should be listed: 14, 15, 18, 20, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 115, 116, 117, 118, 128, 129, 130, 131, 132, 133, 134, 135, 152, 153, 170, 171, 172, 173, 175, 176, 177, 178. If any of the audit event IDs required above is not listed, this is a finding. Notes: 1. It is acceptable to have the required event IDs spread across multiple traces, provided all of the traces are always active, and the event IDs are grouped in a logical manner. 2. It is acceptable, from an auditing point of view, to include the same event IDs in multiple traces. However, the effect of this redundancy on performance, storage, and the consolidation of audit logs into a central repository, should be taken into account. 3. It is acceptable to trace additional event IDs. This is the minimum list. 4. Once this check is satisfied, the DBA may find it useful to disable or modify the default trace that is set up by the SQL Server installation process. (Note that the Fix does NOT include code to do this.) Use the following query to obtain a list of all event IDs, and their meaning: SELECT * FROM sys.trace_events; 5. Because this check procedure is designed to address multiple requirements/vulnerabilities, it may appear to exceed the needs of some individual requirements. However, it does represent the aggregate of all such requirements. 6. Microsoft has flagged the trace techniques and tools used in this Check and Fix as deprecated. They will be removed at some point after SQL Server 2014. The replacement feature is Extended Events. If Extended Events are in use, and cover all the required audit events listed above, this is not a finding.
Create a trace that meets all auditing requirements. The script provided in the supplemental file, Trace.sql, can be used to do this; edit it as necessary to capture any additional, locally defined events.
Check to see that all required events are being audited. From the query prompt: SELECT DISTINCT traceid FROM sys.fn_trace_getinfo(0); All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace(s) being used for the auditing requirement. In the following, replace # with a trace ID being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#); The following required event IDs should be listed: 14, 15, 18, 20, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 115, 116, 117, 118, 128, 129, 130, 131, 132, 133, 134, 135, 152, 153, 170, 171, 172, 173, 175, 176, 177, 178. If any of the audit event IDs required above is not listed, this is a finding. Notes: 1. It is acceptable to have the required event IDs spread across multiple traces, provided all of the traces are always active, and the event IDs are grouped in a logical manner. 2. It is acceptable, from an auditing point of view, to include the same event IDs in multiple traces. However, the effect of this redundancy on performance, storage, and the consolidation of audit logs into a central repository, should be taken into account. 3. It is acceptable to trace additional event IDs. This is the minimum list. 4. Once this check is satisfied, the DBA may find it useful to disable or modify the default trace that is set up by the SQL Server installation process. (Note that the Fix does NOT include code to do this.) Use the following query to obtain a list of all event IDs, and their meaning: SELECT * FROM sys.trace_events; 5. Because this check procedure is designed to address multiple requirements/vulnerabilities, it may appear to exceed the needs of some individual requirements. However, it does represent the aggregate of all such requirements. 6. Microsoft has flagged the trace techniques and tools used in this Check and Fix as deprecated. They will be removed at some point after SQL Server 2014. The replacement feature is Extended Events. If Extended Events are in use, and cover all the required audit events listed above, this is not a finding.
Create a trace that meets all auditing requirements. The script provided in the supplemental file, Trace.sql, can be used to do this; edit it as necessary to capture any additional, locally defined events.
Obtain the list of authorized SQL Server accounts in the system documentation. If accounts are determined to be shared, determine if individuals are first individually authenticated. If individuals are not individually authenticated before using the shared account (e.g., by the operating system or possibly by an application making calls to the database), this is a finding. (The key is individual accountability. If this can be traced, this is not a finding.) If accounts are determined to be shared, determine if they are directly accessible to end users. If so, this is a finding. Review contents of audit logs, traces and data tables to confirm that the identity of the individual user performing the action is captured. If shared identifiers are found, and not accompanied by individual identifiers, this is a finding. Note: Privileged installation accounts may be required to be accessed by the DBA or other administrators for system maintenance. In these cases, each use of the account must be logged in some manner to assign accountability for any actions taken during the use of the account.
Remove user-accessible shared accounts and use individual userids. Build/configure applications to ensure successful individual authentication prior to shared account access. Ensure each user's identity is received and used in audit data in all relevant circumstances. Design, develop, and implement a method to log use of any account to which more than one person has access. Restrict interactive access to shared accounts to the fewest persons possible.
Check to see that all required events are being audited. From the query prompt: SELECT DISTINCT traceid FROM sys.fn_trace_getinfo(0); All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace(s) being used for the auditing requirement. In the following, replace # with a trace ID being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#); The following required event IDs should be listed: 14, 15, 18, 20, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 115, 116, 117, 118, 128, 129, 130, 131, 132, 133, 134, 135, 152, 153, 170, 171, 172, 173, 175, 176, 177, 178. If any of the audit event IDs required above is not listed, this is a finding. Notes: 1. It is acceptable to have the required event IDs spread across multiple traces, provided all of the traces are always active, and the event IDs are grouped in a logical manner. 2. It is acceptable, from an auditing point of view, to include the same event IDs in multiple traces. However, the effect of this redundancy on performance, storage, and the consolidation of audit logs into a central repository, should be taken into account. 3. It is acceptable to trace additional event IDs. This is the minimum list. 4. Once this check is satisfied, the DBA may find it useful to disable or modify the default trace that is set up by the SQL Server installation process. (Note that the Fix does NOT include code to do this.) Use the following query to obtain a list of all event IDs, and their meaning: SELECT * FROM sys.trace_events; 5. Because this check procedure is designed to address multiple requirements/vulnerabilities, it may appear to exceed the needs of some individual requirements. However, it does represent the aggregate of all such requirements. 6. Microsoft has flagged the trace techniques and tools used in this Check and Fix as deprecated. They will be removed at some point after SQL Server 2014. The replacement feature is Extended Events. If Extended Events are in use, and cover all the required audit events listed above, this is not a finding.
Create a trace that meets all auditing requirements. The script provided in the supplemental file, Trace.sql, can be used to do this; edit it as necessary to capture any additional, locally defined events.
To determine the Server Authentication Mode, execute the following: EXEC XP_LOGINCONFIG 'login mode' If the config_value does not equal "Windows NT Authentication", this is a finding.
From SQL Server Management Studio, right-click the server, and then click Properties. Select the Security page. Under Server authentication, select Windows Authentication Mode, and then click OK.
Verify the SQL Server default 'sa' account name has been changed. Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins. If SQL Server default 'sa' account name is in the 'Logins' list, this is a finding.
Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> click 'sa' account name. Hit <F2> while the name is highlighted in order to edit the name. Rename the 'sa' account.
Review system documentation to identify the installation account. Verify whether the account is used for anything beyond SQL Server software installation, upgrade, and maintenance actions. If the account is used for anything beyond SQL Server installation, upgrade, and maintenance actions, this is a finding.
Restrict usage of the SQL Server installation account to SQL Server installation, upgrade, and maintenance actions only. Disable installation accounts when authorized actions are not being performed.
From the system security documentation, obtain the list of SQL Server DBA accounts, the OS/domain Group(s) representing those DBAs' job role(s), and the OS permissions required by that/those role(s). To review local accounts and groups: Log on to the Windows server hosting SQL Server, using an account with administrator privileges. From a command prompt opened as administrator, type gpedit.msc, and press [ENTER]. In Group Policy Editor, navigate to Local Computer Policy > Computer Configuration > Windows Settings > Security Settings > Local Policies > User Rights Assignment. Scan the list to determine which privileges are assigned to the Group(s) representing the SQL Server DBA job role(s). If any privileges are assigned that are not required by these roles, this is a finding. From the command prompt, type lusrmgr.msc, and press [ENTER]. In the Local Users and Groups console, navigate to Users. Right-click each DBA user. Click Properties. Click the 'Member of' tab. If any parent groups are listed that are not specific to DBA roles, this is a finding. In the Local Users and Groups console, navigate to Groups. Right-click each DBA Group. Click Properties. Review the list of group members. If any account that does not represent a DBA is listed, this is a finding. To review domain-level accounts and groups: Log on to a domain controller with the necessary privileges. Open Active Directory Users and Computers (available from menus or run dsa.msc) Determine the location of the accounts or groups to be reviewed. The default is the Users container, but they could have been created or moved to an Organizational Unit (OU) that is domain specific. Right-click each DBA user. Click Properties. Click the 'Member of' tab. If any parent groups are listed that are not specific to DBA roles, this is a finding. Right-click each DBA Group. Click Properties. Select the 'Members' tab. Review the list of group members. If any account that does not represent a DBA is listed, this is a finding.
Remove any unnecessary privileges and any unauthorized members from the Group(s) representing DBAs. Remove any unnecessary Group memberships from the user accounts representing DBAs.
Determine which OS or domain accounts are used by SQL Server to run external procedures. Validate that these accounts have only the privileges necessary to perform the required functionality. If any OS or domain accounts utilized by SQL Server are running external procedures and have privileges beyond those required for running the external procedures, this is a finding.
Limit privileges to SQL Server-related OS and domain accounts to those required privileges needed to perform their SQL Server-specific functionality.
Obtain the list of all DBAs. Obtain documented role assignments for each DBA. Obtain from system documentation or use SQL Server to determine privilege assignment of user-defined roles. Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> right click <'administrator account name'> >> Properties >> User >> Securables. If any item in the 'Permission' listing, for each highlighted item that exists in the 'Securables' listing, has excessive privileges, this is a finding. Navigate from 'Securables' to 'Server Roles'. If any checked 'Server roles' are determined to be excessive privileges, this is a finding. Navigate from 'Server Roles' to 'Users mapped to the login'. If any checked 'Database role membership' of each highlighted and checked 'Database' are determined to be excessive privileges, this is a finding.
Remove permissions from DBAs and other administrative users beyond those required for administrative functions. Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> right click <'administrator account name'> >> Properties >> User >> Securables. Remove 'Securables' permissions from DBAs and other administrative users that are beyond what is required. Navigate from 'Securables' to 'Server Roles'. Remove 'Server Roles' permissions from DBAs and other administrative users that are beyond what is required. Navigate from 'Server Roles' to 'Users mapped to the login'. Remove 'Users mapped to the login' permissions from DBAs and other administrative users that are beyond what is required.
Check to see that all required events are being audited. From the query prompt: SELECT DISTINCT traceid FROM sys.fn_trace_getinfo(0); All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace(s) being used for the auditing requirement. In the following, replace # with a trace ID being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#); The following required event IDs should be listed: 14, 15, 18, 20, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 115, 116, 117, 118, 128, 129, 130, 131, 132, 133, 134, 135, 152, 153, 170, 171, 172, 173, 175, 176, 177, 178. If any of the audit event IDs required above is not listed, this is a finding. Notes: 1. It is acceptable to have the required event IDs spread across multiple traces, provided all of the traces are always active, and the event IDs are grouped in a logical manner. 2. It is acceptable, from an auditing point of view, to include the same event IDs in multiple traces. However, the effect of this redundancy on performance, storage, and the consolidation of audit logs into a central repository, should be taken into account. 3. It is acceptable to trace additional event IDs. This is the minimum list. 4. Once this check is satisfied, the DBA may find it useful to disable or modify the default trace that is set up by the SQL Server installation process. (Note that the Fix does NOT include code to do this.) Use the following query to obtain a list of all event IDs, and their meaning: SELECT * FROM sys.trace_events; 5. Because this check procedure is designed to address multiple requirements/vulnerabilities, it may appear to exceed the needs of some individual requirements. However, it does represent the aggregate of all such requirements. 6. Microsoft has flagged the trace techniques and tools used in this Check and Fix as deprecated. They will be removed at some point after SQL Server 2014. The replacement feature is Extended Events. If Extended Events are in use, and cover all the required audit events listed above, this is not a finding.
Create a trace that meets all auditing requirements. The script provided in the supplemental file, Trace.sql, can be used to do this; edit it as necessary to capture any additional, locally defined events.
Obtain a list of SQL Server DBAs or other administrative accounts. Run the following SQL script to check all users’ permissions: SELECT SP1.[name] AS 'Login', 'Role: ' + SP2.[name] COLLATE DATABASE_DEFAULT AS 'ServerPermission' FROM sys.server_principals SP1 JOIN sys.server_role_members SRM ON SP1.principal_id = SRM.member_principal_id JOIN sys.server_principals SP2 ON SRM.role_principal_id = SP2.principal_id UNION ALL SELECT SP.[name] AS 'Login' , SPerm.state_desc + ' ' + SPerm.permission_name COLLATE DATABASE_DEFAULT AS 'ServerPermission' FROM sys.server_principals SP JOIN sys.server_permissions SPerm ON SP.principal_id = SPerm.grantee_principal_id ORDER BY [Login], [ServerPermission] If any DBA or administrative objects are owned by non-DBA or non-administrative accounts, this is a finding. If any DBA or administrator has authorization for non- administrative access to the system for which they are the administrator and they do not have a non-administrator account, this is a finding.
Remove DBA privileges and privileges to administer owned objects that are assigned to the administrator's non-DBA account. Remove the permission access from the account that has direct access by running the following script: USE master REVOKE <'server privilege name'> TO <'account name'> GO Remove the user account from the role's Member list where the account is not authorized for specified permission by running the following script: USE master ALTER SERVER ROLE [<'server role name'>] DROP MEMBER <'user name'> GO Provide administrators with separate accounts for administration and regular accounts for non-administrator activity.
Use SQL Server and system documentation to determine privilege assignment of user-defined roles. Determine which user-defined roles grant privileges to system tables and configuration data stored in SQL Server. For each Login: In SQL Server Management Studio, Object Explorer, expand <SQL Server instance> >> Security >> Logins >> Right-click <login account name> >> Properties >> User >> Securables. If any item in the Explicit Permissions listing, for each highlighted item that exists in the Securables listing, indicates direct permission access, and that permission is anything other than Connect SQL, this is a finding. Navigate from Securables to Server Roles. If any Server Roles are checked from the following list, indicating direct permission access, this is a finding: bulkadmin dbcreator diskadmin processadmin securityadmin serveradmin setupadmin If the sysadmin server role is checked, review system documentation to determine whether this login's need for the sysadmin role is documented and approved. If it is not, this is a finding. If any user-defined server roles with system table or configuration data privileges are checked, review system documentation to determine whether this login's need for the role is documented and approved. If it is not, this is a finding. Navigate from Server Roles to User Mapping. Select in turn each entry where the User column is non-blank. If any Database Roles are checked from the following list, indicating direct permission access, this is a finding: db_accessadmin db_backupoperator db_datareader db_datawriter db_ddladmin db_denydatareader db_denydatawriter db_owner db_securityadmin
If necessary memberships in the sysadmin role are not documented or not approved, document them and obtain approval. If unnecessary memberships in the sysadmin role are documented, remove them from the documentation. Remove all direct access permissions and unauthorized permissions as required using the below instructions: In SQL Server Management Studio, Object Explorer, expand <SQL Server instance> >> Security >> Logins >> Right-click <user account name> >> Properties >> User >> Securables. Remove Securables permissions from user account. Navigate from Securables to Server Roles. Remove Server Roles permissions from user account. Navigate from Server Roles to Users Mapping. Remove direct permissions on db_accessadmin, db_backupoperator, db_datareader, db_datawriter, db_ddladmin, db_denydatareader, db_denydatawriter, db_owner, and db_securityadmin from user account.
Check procedures for providing SQL Server database connection information to users/applications. If procedures do not indicate or implement restrictions to connections required by the particular user/application which indicate process of least privilege and specific authorization was employed, this is a finding.
Implement procedures to supply SQL Server database connection information to only those databases authorized for the user.
Obtain the list of available user-defined server roles from system documentation. Obtain the list of available user-defined server roles from the SQL Server system by running the following script: /********************************************************************************** LIST ALL INDIRECT (via ROLES) ACCESS TO THE SERVER PERMISSION. ***********************************************************************************/ DECLARE @admin_Account_name sysname SET @admin_Account_name = 'NO admin ACCOUNT found' DECLARE @server_name sysname SET @server_name = 'NO Server found' SELECT @server_name = name FROM sys.servers WHERE server_id = 0 SET @admin_Account_name = @server_name + '\Administrator' SELECT pe.grantee_principal_id , pr.type AS 'Grantee_Type' , pr.name AS 'Grantee_Name' , pe.type , pe.permission_name , pe.state , pe.state_desc FROM sys.server_permissions pe JOIN sys.server_principals pr ON pe.grantee_principal_id = pr.principal_id JOIN sys.server_principals ps ON pe.grantor_principal_id = ps.principal_id LEFT JOIN sys.server_principals us ON us.principal_id = pe.major_id WHERE pr.type IN ('R') AND pe.grantee_principal_id > 10 AND NOT pr.name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##', 'NT AUTHORITY\NETWORK SERVICE', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\MSSQLSERVER', 'NT SERVICE\SQLSERVERAGENT', 'NT SERVICE\SQLWriter', 'NT SERVICE\Winmgmt') AND NOT pr.name = @admin_Account_name ORDER BY CASE pe.state WHEN 'D' THEN 1 WHEN 'W' THEN 2 WHEN 'G' THEN 3 ELSE 4 END If any listed user-defined roles are not found in the system documentation, this is a finding. Obtain the list assigned privileges for all user-defined roles in the system documentation. Check all SQL Server user-defined server roles for access rights as it relates to the separation of duties. Repeat steps for each user-defined server role. Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Server Roles >> right click <'user-defined server role name'> >> Properties >> General >> Securables. If any user-defined role is assigned privileges that are not documented in the system documentation, this is a finding. If any user-defined role contains permissions that are inconsistent with separation sensitive information assignment, this is a finding. If system access requires more than one level of sensitive information access and the user-defined role names do not clearly differentiate between the different levels of sensitive information, this is a finding.
Add the user-defined server role to the system documentation. Add the assigned privileges of the user-defined server role to the system documentation. Remove the user from direct access to server permission by running the following script: USE master REVOKE <'server permission name'> TO <'account name'> CASCADE Remove server role permission from the user-defined server role by running the following script: USE master REVOKE <'server role name'> TO [<'server role name'>] Rename the user-defined role by running the following script: USE master ALTER SERVER ROLE [<'old role name'>] WITH NAME = [<'new role name'>]
Check OS settings to determine whether SQL Server processes are running under a dedicated OS or domain account. If the SQL Server processes are running under shared accounts, this is a finding. From a Command Prompt, type services.msc, and press [ENTER]. Scroll down to the SQL Server Services. SQL Server Services begin with SQL. The following services, when present, should be listed as follows: Service Name: Log On As: SQL Full-text Filter Daemon Launcher: NT Service\UNIQUE CUSTOM ACCOUNT SQL Server [stand-alone]: NT Service\UNIQUE CUSTOM ACCOUNT SQL Server [cluster]: <domain>\<CustomServiceAccount> SQL Server Agent: NT Service\UNIQUE CUSTOM ACCOUNT SQL Server Analysis Services: NT Service\UNIQUE CUSTOM ACCOUNT SQL Server Browser: Local Service SQL Server Distributed Replay Client: NT Service\UNIQUE CUSTOM ACCOUNT SQL Server Distributed Replay Controller: NT Service\UNIQUE CUSTOM ACCOUNT SQL Server Integration Services 11.0: NT Service\UNIQUE CUSTOM ACCOUNT SQL Server Reporting Services: NT Service\UNIQUE CUSTOM ACCOUNT SQL Server VSS Writer: Local System UNIQUE CUSTOM ACCOUNT refers to an account with which no other service listed in the services.msc window is assigned. If any account requiring a unique custom account uses an account that any other service utilizes (regardless of service status), this is a finding.
Configure the SQL Server services to use a custom, dedicated OS or domain account.
Check for direct user assignment to server permissions by running the following script: /********************************************************************************** LIST ALL DIRECT SERVER PERMISSIONS TO ANY ACCOUNT EXCEPT SYSTEM ADMINISTRATOR accounts. DO NOT LIST ROLES. ***********************************************************************************/ DECLARE @admin_Account_name sysname SET @admin_Account_name = 'NO administrator account found' DECLARE @server_name sysname SET @server_name = 'NO Server found' SELECT @server_name = name FROM sys.servers WHERE server_id = 0 SET @admin_Account_name = @server_name + '\Administrator' SELECT pe.grantee_principal_id , pr.type AS 'Grantee_Type' , pr.name AS 'Grantee_Name' , pe.type , pe.permission_name , pe.state , pe.state_desc FROM sys.server_permissions pe JOIN sys.server_principals pr ON pe.grantee_principal_id = pr.principal_id JOIN sys.server_principals ps ON pe.grantor_principal_id = ps.principal_id LEFT JOIN sys.server_principals us ON us.principal_id = pe.major_id WHERE pr.type IN ('K', 'S', 'U') AND pe.grantee_principal_id > 10 AND NOT pr.name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##', 'NT AUTHORITY\NETWORK SERVICE', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\MSSQLSERVER', 'NT SERVICE\SQLSERVERAGENT', 'NT SERVICE\SQLWriter', 'NT SERVICE\Winmgmt') AND NOT pr.name = @admin_Account_name AND NOT pe.permission_name = 'connect sql' ORDER BY CASE pr.type WHEN 'K' THEN 1 WHEN 'S' THEN 2 WHEN 'U' THEN 3 ELSE 4 END If any user account list indicates direct access to any server permission, this is a finding. Obtain the list of available user-defined server roles from system documentation. Obtain the list of available user-defined server roles from the SQL Server system by running the following script: /********************************************************************************** LIST ALL INDIRECT (via ROLES) ACCESS TO THE SERVER PERMISSION. ***********************************************************************************/ DECLARE @admin_Account_name sysname SET @admin_Account_name = 'NO admin ACCOUNT found' DECLARE @server_name sysname SET @server_name = 'NO Server found' SELECT @server_name = name FROM sys.servers WHERE server_id = 0 SET @admin_Account_name = @server_name + '\Administrator' SELECT pe.grantee_principal_id , pr.type AS 'Grantee_Type' , pr.name AS 'Grantee_Name' , pe.type , pe.permission_name , pe.state , pe.state_desc FROM sys.server_permissions pe JOIN sys.server_principals pr ON pe.grantee_principal_id = pr.principal_id JOIN sys.server_principals ps ON pe.grantor_principal_id = ps.principal_id LEFT JOIN sys.server_principals us ON us.principal_id = pe.major_id WHERE pr.type IN ('R') AND pe.grantee_principal_id > 10 AND NOT pr.name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##', 'NT AUTHORITY\NETWORK SERVICE', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\MSSQLSERVER', 'NT SERVICE\SQLSERVERAGENT', 'NT SERVICE\SQLWriter', 'NT SERVICE\Winmgmt') AND NOT pr.name = @admin_Account_name AND NOT pe.permission_name = 'connect sql' ORDER BY CASE pe.state WHEN 'D' THEN 1 WHEN 'W' THEN 2 WHEN 'G' THEN 3 ELSE 4 END If any listed user-defined roles are not found in the system documentation, this is a finding. Obtain the list of assigned privileges for all user-defined roles in the system documentation. Check all SQL Server user-defined server roles for access rights as it relates to the separation of duties. Repeat steps for each user-defined server role. Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Server Roles >> right click <'user-defined server role name'> >> Properties >> General >> Securables. If any roles are found that do not enforce separation of duties, this is a finding.
Add the user-defined server role to the system documentation. Add the assigned privileges of the user-defined server role to the system documentation. Remove the user from direct access to server permission by running the following script: USE master REVOKE <'server permission name'> TO <'account name'> CASCADE Remove server role permission from the user-defined server role by running the following script: USE master REVOKE <'server role name'> TO [<'server role name'>]
Check for rights propagation assignment to DBMS server permissions by running the following query: USE master; SELECT * FROM sys.server_permissions WHERE state_desc = 'GRANT_WITH_GRANT_OPTION'; If any of the permissions listed have not been documented and approved as requiring GRANT_WITH_GRANT_OPTION, this is a finding.
Document and obtain approval for each GRANT_WITH_GRANT_OPTION that is required. Correct each unapproved GRANT_WITH_GRANT_OPTION with REVOKE and GRANT statements of the form (replacing "ALTER ANY DATABASE" with the actual server permission at issue): REVOKE ALTER ANY DATABASE FROM SampleLoginOrServerRole CASCADE; GRANT ALTER ANY DATABASE TO SampleServerRole; -- Note, no WITH GRANT OPTION clause here.
Check for direct user assignment to server permissions by running the following script: /********************************************************************************** LIST ALL DIRECT SERVER PERMISSIONS TO ANY ACCOUNT EXCEPT SYSTEM ADMINISTRATOR accounts. DO NOT LIST ROLES. ***********************************************************************************/ DECLARE @admin_Account_name sysname SET @admin_Account_name = 'NO Administrator account found' DECLARE @server_name sysname SET @server_name = 'NO Server found' SELECT @server_name = name FROM sys.servers WHERE server_id = 0 SET @admin_Account_name = @server_name + '\Administrator' SELECT pe.grantee_principal_id , pr.type AS 'Grantee_Type' , pr.name AS 'Grantee_Name' , pe.type , pe.permission_name , pe.state , pe.state_desc FROM sys.server_permissions pe JOIN sys.server_principals pr ON pe.grantee_principal_id = pr.principal_id JOIN sys.server_principals ps ON pe.grantor_principal_id = ps.principal_id LEFT JOIN sys.server_principals us ON us.principal_id = pe.major_id WHERE pr.type IN ('K', 'S', 'U') AND pe.grantee_principal_id > 10 AND NOT pr.name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##', 'NT AUTHORITY\NETWORK SERVICE', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\MSSQLSERVER', 'NT SERVICE\SQLSERVERAGENT', 'NT SERVICE\SQLWriter', 'NT SERVICE\Winmgmt') AND NOT pr.name = @admin_Account_name ORDER BY CASE pr.type WHEN 'K' THEN 1 WHEN 'S' THEN 2 WHEN 'U' THEN 3 ELSE 4 END If any user account list indicates direct access to any server permission, this is a finding. Obtain the list of available user-defined server roles from system documentation. Obtain the list of available user-defined server roles from the SQL Server system by running the following script: /********************************************************************************** LIST ALL INDIRECT (via ROLES) ACCESS TO THE SERVER PERMISSION. ***********************************************************************************/ DECLARE @admin_Account_name sysname SET @admin_Account_name = 'NO admin ACCOUNT found' DECLARE @server_name sysname SET @server_name = 'NO Server found' SELECT @server_name = name FROM sys.servers WHERE server_id = 0 SET @admin_Account_name = @server_name + '\Administrator' SELECT pe.grantee_principal_id , pr.type AS 'Grantee_Type' , pr.name AS 'Grantee_Name' , pe.type , pe.permission_name , pe.state , pe.state_desc FROM sys.server_permissions pe JOIN sys.server_principals pr ON pe.grantee_principal_id = pr.principal_id JOIN sys.server_principals ps ON pe.grantor_principal_id = ps.principal_id LEFT JOIN sys.server_principals us ON us.principal_id = pe.major_id WHERE pr.type IN ('R') AND pe.grantee_principal_id > 10 AND NOT pr.name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##', 'NT AUTHORITY\NETWORK SERVICE', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\MSSQLSERVER', 'NT SERVICE\SQLSERVERAGENT', 'NT SERVICE\SQLWriter', 'NT SERVICE\Winmgmt') AND NOT pr.name = @admin_Account_name ORDER BY CASE pe.state WHEN 'D' THEN 1 WHEN 'W' THEN 2 WHEN 'G' THEN 3 ELSE 4 END If any listed user-defined roles are not found in the system documentation, this is a finding. Obtain the list of user role assignments in the system documentation. Check all SQL Server user-defined server roles for authorized and documented permission assignments. Repeat steps for each user-defined server role. Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Server Roles >> right click <'user-defined server role name'> >> Properties >> Members. If any roles are found that are not authorized and documented, this is a finding.
Add the user-defined server role to the system documentation. Add the user as a member of the user-defined server role within the system documentation. Remove the user from direct access to server permission by running the following script: USE master REVOKE <'server permission name'> TO <'account name'> CASCADE Remove the user from user-defined role access by running the following script: USE master ALTER SERVER ROLE [<'server role name'>] DROP MEMBER <'user name'> Add the user-defined role access to the user by running the following script: USE master ALTER SERVER ROLE [<'server role name'>] ADD MEMBER <'user name'>
Obtain the list of roles that are authorized for the SQL Server 'Unsafe assembly' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized. Obtain the list of roles with that permission by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Unsafe assembly' AND who.type_desc = 'SERVER_ROLE' ORDER BY who.name ; GO If any role has 'Grant', 'With Grant' or 'Deny' privileges to the 'Unsafe assembly' permission and the role is not authorized to have the permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.type_desc = 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Unsafe assembly' permission access from the role that is not authorized by executing the following query: REVOKE Unsafe assembly TO <'role name'>
Obtain the list of accounts that have direct access to the server-level permission 'Alter any endpoint' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Alter any endpoint' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'Alter any endpoint' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Alter any endpoint' permission access from the account that has direct access by running the following script: USE master REVOKE ALTER ANY ENDPOINT TO <'account name'> GO
Obtain the list of accounts that have direct access to the server-level permission 'Alter any database' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Alter any database' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'Alter any database' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Alter any database' permission access from the account that has direct access by running the following script: USE master REVOKE ALTER ANY DATABASE TO <'account name'> GO
Obtain the list of accounts that have direct access to the server-level permission 'Alter any credential' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Alter any credential' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'Alter any credential' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Alter any credential' permission access from the account that has direct access by running the following script: USE master REVOKE ALTER ANY CREDENTIAL TO <'account name'> GO
Obtain the list of accounts that have direct access to the server-level permission 'Alter any connection' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Alter any connection' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'Alter any connection' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Alter any connection' permission access from the account that has direct access by running the following script: USE master REVOKE ALTER ANY CONNECTION TO <'account name'> GO
Obtain the list of accounts that have direct access to the server-level permission 'Alter any availability group' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Alter any availability group' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'Alter any availability group' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Alter Any Availability Group' permission access from the account that has direct access by using the following code. Substitute the relevant names for the text in angle brackets. -- For each login identified in the Check: USE master; REVOKE ALTER ANY AVAILABILITY GROUP FROM <login name>; GO -- If the necessary server role does not already exist, -- and any user identified in the Check needs this permission: USE master; CREATE SERVER ROLE <role name> AUTHORIZATION <appropriate principal name>; GO GRANT ALTER ANY AVAILABILITY GROUP TO <role name>; GO -- For each user identified in the Check who needs this permission: USE master; ALTER SERVER ROLE <role name> ADD MEMBER <login name>; GO
Obtain the list of accounts that have direct access to the server-level permission 'Alter server state' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Alter server state' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'Alter server state' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Alter server state' permission access from the account that has direct access by running the following script: USE master REVOKE ALTER SERVER STATE TO <'account name'> GO
Obtain the list of accounts that have direct access to the server-level permission 'Alter any event notification' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Alter any event notification' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'Alter any event notification' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Alter any event notification' permission access from the account that has direct access by running the following script: USE master REVOKE ALTER ANY EVENT NOTIFICATION TO <'account name'> GO
Obtain the list of roles that are authorized for the SQL Server 'View any database' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized. Obtain the list of roles with that permission by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'View any database' AND who.type_desc = 'SERVER_ROLE' ORDER BY who.name ; GO If any role has 'Grant', 'With Grant' or 'Deny' privileges on this permission and users with that role are not authorized to have the permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.type_desc = 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the "View any database" permission access from the role that is not authorized by executing the following query: REVOKE View any database TO <'role name'>
Obtain the list of accounts that have direct access to the server-level permission 'Alter any server audit' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Alter any server audit' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'Alter any server audit' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Alter any server audit' permission access from the account that has direct access by running the following script: USE master REVOKE ALTER ANY SERVER AUDIT TO <'account name'> GO
Obtain the list of roles that are authorized for the 'Shutdown' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized. Obtain the list of roles with that permission by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Shutdown' AND who.type_desc = 'SERVER_ROLE' ORDER BY who.name ; GO If any role has 'Grant', 'With Grant' or 'Deny' privileges on this permission and users with that role are not authorized to have the permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.type_desc = 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the' 'Shutdown' permission access from the role that is not authorized by executing the following query: REVOKE Shutdown TO <'role name'>
Obtain the list of roles that are authorized for the SQL Server 'External access assembly' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized. Obtain the list of roles with that permission by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'External access assembly' AND who.type_desc = 'SERVER_ROLE' ORDER BY who.name ; GO If any role has 'Grant', 'With Grant' or 'Deny' privileges on this permission and users with that role are not authorized to have the permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.type_desc = 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'External access assembly' permission access from the role that is not authorized by executing the following query: REVOKE External access assembly TO <'role name'>
Obtain the list of roles that are authorized for the SQL Server 'Create trace event notification' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized. Obtain the list of roles with that permission by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Create trace event notification' AND who.type_desc = 'SERVER_ROLE' ORDER BY who.name ; GO If any role has 'Grant', 'With Grant' or 'Deny' privileges on this permission and users with that role are not authorized to have the permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.type_desc = 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Create trace event notification' permission access from the role that is not authorized by executing the following query: REVOKE Create trace event notification TO <'role name'>
Obtain the list of roles that are authorized for the SQL Server 'Create server role' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized. Obtain the list of roles with that permission by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Create server role' AND who.type_desc = 'SERVER_ROLE' ORDER BY who.name ; GO If any role has 'Grant', 'With Grant' or 'Deny' privileges on this permission and users with that role are not authorized to have the permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.type_desc = 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Create server role' permission access from the role that is not authorized by executing the following query: REVOKE Create server role TO <'role name'>
Obtain the list of roles that are authorized for the SQL Server 'Create endpoint' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized. Obtain the list of roles with that permission by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Create endpoint' AND who.type_desc = 'SERVER_ROLE' ORDER BY who.name ; GO If any role has 'Grant', 'With Grant' or 'Deny' privileges on this permission and users with that role are not authorized to have the permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.type_desc = 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Create endpoint' permission access from the role that is not authorized by executing the following query: REVOKE Create endpoint TO <'role name'>
Obtain the list of roles that are authorized for the SQL Server 'Create DDL event notification' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized. Obtain the list of roles with that permission by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Create DDL event notification' AND who.type_desc = 'SERVER_ROLE' ORDER BY who.name ; GO If any role has 'Grant', 'With Grant' or 'Deny' privileges on this permission and users with that role are not authorized to have the permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.type_desc = 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Create DDL event notification' permission access from the role that is not authorized by executing the following query: REVOKE Create DDL event notification TO <'role name'>
Obtain the list of roles that are authorized for the SQL Server 'Create availability group' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized. Obtain the list of roles with that permission by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Create availability group' AND who.type_desc = 'SERVER_ROLE' ORDER BY who.name ; GO If any role has 'Grant', 'With Grant' or 'Deny' privileges on this permission and users with that role are not authorized to have the permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.type_desc = 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Create availability group' permission access from the role that is not authorized by executing the following query: REVOKE Create availability group TO <'role name'>
Obtain the list of roles that are authorized for the SQL Server 'Alter any server audit' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized. Obtain the list of roles with that permission by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Alter any server audit' AND who.type_desc = 'SERVER_ROLE' ORDER BY who.name ; GO If any role has 'Grant', 'With Grant' or 'Deny' privileges on this permission and users with that role are not authorized to have the permission, this is a finding.
Remove the 'Alter any server audit' permission access from the role that is not authorized by executing the following query: REVOKE Alter any server audit TO <'role name'>
Obtain the list of roles that are authorized for the SQL Server 'View any definition' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized. Obtain the list of roles with that permission by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'View any definition' AND who.type_desc = 'SERVER_ROLE' ORDER BY who.name ; GO If any role has 'Grant', 'With Grant' or 'Deny' privileges on this permission and users with that role are not authorized to have the permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.type_desc = 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'View any definition' permission access from the role that is not authorized by executing the following query: REVOKE View any definition TO <'role name'>
Obtain the list of accounts that have direct access to the server-level permission 'Authenticate Server' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'AUTHENTICATE SERVER' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'Authenticate Server' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Authenticate Server' permission access from the account that has direct access by running the following script: USE master; REVOKE AUTHENTICATE SERVER FROM <account name>; GO
Obtain the list of accounts that have direct access to the server-level permission 'Administer bulk operations' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Administer bulk operations' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'Administer bulk operations' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Administer bulk operations' permission access from the account that has direct access by running the following script: USE master REVOKE ADMINISTER BULK OPERATIONS TO <'account name'> GO
Obtain the list of accounts that have direct access to the server-level permission 'Create endpoint' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Create endpoint' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'Create endpoint' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Create endpoint' permission access from the account that has direct access by running the following script: USE master REVOKE CREATE ENDPOINT TO <'account name'> GO
Obtain the list of accounts that have direct access to the server-level permission 'Create DDL Event Notification' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Create DDL Event Notification' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'Create DDL Event Notification' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Create DDL event notification' permission access from the account that has direct access by running the following script: USE master REVOKE CREATE DDL EVENT NOTIFICATION TO <'account name'> GO
Obtain the list of accounts that have direct access to the server-level permission 'Create availability group' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Create availability group' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'Create availability group' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Create availability group' permission access from the account that has direct access by running the following script: USE master REVOKE CREATE AVAILABILITY GROUP TO <'account name'> GO
Obtain the list of accounts that have direct access to the server-level permission 'Create any database' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Create any database' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'Create any database' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Create any database'" permission access from the account that has direct access by running the following script: USE master REVOKE CREATE ANY DATABASE TO <'account name'> GO
Obtain the list of accounts that have direct access to the server-level permission 'Control server' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Control server' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'Control server' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Control server' permission access from the account that has direct access by running the following script: USE master REVOKE CONTROL SERVER TO <'account name'> GO
Obtain the list of roles that are authorized for the SQL Server 'Administer bulk operations' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized. Obtain the list of roles with that permission by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Administer bulk operations' AND who.type_desc = 'SERVER_ROLE' ORDER BY who.name ; GO If any role has 'Grant', 'With Grant' or 'Deny' privileges on this permission and users with that role are not authorized to have the permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.type_desc = 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Administer bulk operations' permission access from the role that is not authorized by executing the following query: REVOKE Administer bulk operations TO <'role name'>
Obtain the list of roles that are authorized for the SQL Server 'Alter resources' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized. Obtain the list of roles with that permission by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Alter resources' AND who.type_desc = 'SERVER_ROLE' ORDER BY who.name ; GO If any role has 'Grant', 'With Grant' or 'Deny' privileges on this permission and users with that role are not authorized to have the permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.type_desc = 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Alter resources' permission access from the role that is not authorized by executing the following query: REVOKE Alter resources TO <'role name'>
Obtain the list of accounts that have direct access to the server-level permission 'Alter any linked server' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Alter any linked server' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'Alter any linked server' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Alter any linked server' permission access from the account that has direct access by running the following script: USE master REVOKE ALTER ANY LINKED SERVER TO <'account name'> GO
Obtain the list of accounts that have direct access to the server-level permission 'Alter any event session' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Alter any event session' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'Alter any event session' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Alter any event session' permission access from the account that has direct access by running the following script: USE master REVOKE ALTER ANY EVENT SESSION TO <'account name'> GO
Obtain the list of accounts that have direct access to the server-level permission 'Alter trace' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Alter trace' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'Alter trace' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Alter trace' permission access from the account that has direct access by running the following script: USE master REVOKE ALTER TRACE TO <'account name'> GO
Obtain the list of accounts that have direct access to the server-level permission 'Alter Settings' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Alter Settings' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'Alter Settings' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Alter Settings' permission access from the account that has direct access by running the following script: USE master REVOKE ALTER SETTINGS TO <'account name'> GO
Obtain the list of accounts that have direct access to the server-level permission 'Create trace event notification' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Create trace event notification' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'Create trace event notification' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Create trace event notification' permission access from the account that has direct access by running the following script: USE master REVOKE CREATE TRACE EVENT NOTIFICATION TO <'account name'> GO
Obtain the list of accounts that have direct access to the server-level permission 'Alter resources' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Alter resources' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'Alter resources' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Alter resources' permission access from the account that has direct access by running the following script: USE master REVOKE ALTER RESOURCES TO <'account name'> GO
Obtain the list of accounts that have direct access to the server-level permission 'External access assembly' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'External access assembly' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'External access assembly' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'External access assembly' permission access from the account that has direct access by running the following script: USE master REVOKE EXTERNAL ACCESS ASSEMBLY TO <'account name'> GO
Obtain the list of accounts that have direct access to the server-level permission 'Alter any login' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Alter any login' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'Alter any login' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Alter any login' permission access from the account that has direct access by running the following script: USE master REVOKE ALTER ANY LOGIN TO <'account name'> GO
Obtain the list of roles that are authorized for the SQL Server 'Alter any availability group' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized. Obtain the list of roles with that permission by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Alter any availability group' AND who.type_desc = 'SERVER_ROLE' ORDER BY who.name ; GO If any role has 'Grant', 'With Grant' or 'Deny' privileges on this permission and users with that role are not authorized to have the permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.type_desc = 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Alter any availability group' permission access from the role that is not authorized by executing the following query: REVOKE Alter any availability group TO <'role name'>
Obtain the list of roles that are authorized for the SQL Server 'Alter any login' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized. Obtain the list of roles with that permission by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Alter any login' AND who.type_desc = 'SERVER_ROLE' ORDER BY who.name ; GO If any role has 'Grant', 'With Grant' or 'Deny' privileges on this permission and users with that role are not authorized to have the permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.type_desc = 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Alter any login' permission access from the role that is not authorized by executing the following query: REVOKE Alter any login TO <'role name'>
Obtain the list of roles that are authorized for the SQL Server 'Alter any linked server' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized. Obtain the list of roles with that permission by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Alter any linked server' AND who.type_desc = 'SERVER_ROLE' ORDER BY who.name ; GO If any role has 'Grant', 'With Grant' or 'Deny' privileges on this permission and users with that role are not authorized to have the permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.type_desc = 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Alter any linked server' permission access from the role that is not authorized by executing the following query: REVOKE Alter any linked server TO <'role name'>
Obtain the list of accounts that have direct access to the server-level permission 'Shutdown' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Shutdown' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'Shutdown' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Shutdown' permission access from the account that has direct access by running the following script: USE master REVOKE SHUTDOWN TO <'account name'> GO
Obtain the list of roles that are authorized for the SQL Server 'View server state' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized. Obtain the list of roles with that permission by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'View server state' AND who.type_desc = 'SERVER_ROLE' ORDER BY who.name ; GO If any role has 'Grant', 'With Grant' or 'Deny' privileges on this permission and users with that role are not authorized to have the permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.type_desc = 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'View server state' permission access from the role that is not authorized by executing the following query: REVOKE View server state TO <'role name'>
Obtain the list of roles that are authorized for the SQL Server 'Alter trace' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized. Obtain the list of roles with that permission by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Alter trace' AND who.type_desc = 'SERVER_ROLE' ORDER BY who.name ; GO If any role has 'Grant', 'With Grant' or 'Deny' privileges on this permission and users with that role are not authorized to have the permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.type_desc = 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Alter trace' permission access from the role that is not authorized by executing the following query: REVOKE Alter trace TO <'role name'>
Obtain the list of accounts that have direct access to the server-level permission 'Unsafe assembly' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Unsafe assembly' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'Unsafe assembly' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Unsafe assembly' permission access from the account that has direct access by running the following script: USE master REVOKE UNSAFE ASSEMBLY TO <'account name'> GO
Obtain the list of roles that are authorized for the SQL Server 'Control server' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized. Obtain the list of roles with that permission by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Control server' AND who.type_desc = 'SERVER_ROLE' ORDER BY who.name ; GO If any role has 'Grant', 'With Grant' or 'Deny' privileges on this permission and users with that role are not authorized to have the permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.type_desc = 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Control server' permission access from the role that is not authorized by executing the following query: REVOKE Control server TO <'role name'>
Obtain the list of accounts that have direct access to the server-level permission 'Create server role' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Create server role' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'Create server role' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Create server role' permission access from the account that has direct access by running the following script: USE master REVOKE CREATE SERVER ROLE TO <'account name'> GO
Obtain the list of roles that are authorized for the SQL Server 'Alter any server role' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized. Obtain the list of roles with that permission by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Alter any server role' AND who.type_desc = 'SERVER_ROLE' ORDER BY who.name ; GO If any role has 'Grant', 'With Grant' or 'Deny' privileges on this permission and users with that role are not authorized to have the permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.type_desc = 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Alter any server role' permission access from the role that is not authorized by executing the following query: REVOKE Alter any server role TO <'role name'>
Obtain the list of roles that are authorized for the SQL Server 'Alter Settings' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized. Obtain the list of roles with that permission by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Alter Settings' AND who.type_desc = 'SERVER_ROLE' ORDER BY who.name ; GO If any role has 'Grant', 'With Grant' or 'Deny' privileges on this permission and users with that role are not authorized to have the permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.type_desc = 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Alter Settings' permission access from the role that is not authorized by executing the following query: REVOKE Alter Settings TO <'role name'>
Obtain the list of roles that are authorized for the SQL Server 'Authenticate server' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized. Obtain the list of roles with that permission by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Authenticate server' AND who.type_desc = 'SERVER_ROLE' ORDER BY who.name ; GO If any role has 'Grant', 'With Grant' or 'Deny' privileges on this permission and users with that role are not authorized to have the permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.type_desc = 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Authenticate server' permission access from the role that is not authorized by executing the following query: REVOKE Authenticate server TO <'role name'>
Obtain the list of roles that are authorized for the SQL Server 'Create any database' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized. Obtain the list of roles with that permission by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Create any database' AND who.type_desc = 'SERVER_ROLE' ORDER BY who.name ; GO If any role has 'Grant', 'With Grant' or 'Deny' privileges on this permission and users with that role are not authorized to have the permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.type_desc = 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Create any database' permission access from the role that is not authorized by executing the following query: REVOKE Create any database TO <'role name'>
Obtain the list of accounts that have direct access to the server-level permission 'View server state' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'View server state' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user account has direct access to the 'View server state' permission, and the need for this has not been documented and approved, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Document any necessary exceptions, and obtain the appropriate approval. Remove the 'View server state' permission access from an account that has direct access by running the following script: USE master; REVOKE VIEW SERVER STATE TO <'account name'>; GO
Obtain the list of accounts that have direct access to the server-level permission 'Alter any server role' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Alter any server role' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'Alter any server role' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'Alter any server role' permission access from the account that has direct access by running the following script: USE master REVOKE ALTER ANY SERVER ROLE TO <'account name'> GO
Obtain the list of accounts that have direct access to the server-level permission 'View any definition' by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'View any definition' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name ; GO If any user accounts have direct access to the 'View any definition' permission, this is a finding. Alternatively, to provide a combined list for all requirements of this type: SELECT what.permission_name AS [Permission Name], what.state_desc AS [Permission State], who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name IN ( 'Administer bulk operations', 'Alter any availability group', 'Alter any connection', 'Alter any credential', 'Alter any database', 'Alter any endpoint ', 'Alter any event notification ', 'Alter any event session ', 'Alter any linked server', 'Alter any login', 'Alter any server audit', 'Alter any server role', 'Alter resources', 'Alter server state ', 'Alter Settings ', 'Alter trace', 'Authenticate server ', 'Control server', 'Create any database ', 'Create availability group', 'Create DDL event notification', 'Create endpoint', 'Create server role', 'Create trace event notification', 'External access assembly', 'Shutdown', 'Unsafe Assembly', 'View any database', 'View any definition', 'View server state' ) AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY what.permission_name, who.name ; GO
Remove the 'View any definition' permission access from the account that has direct access by running the following script: USE master REVOKE VIEW ANY DEFINITION TO <'account name'> GO
Obtain the list of roles that are authorized for the SQL Server 'Alter any connection' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized. Obtain the list of roles with that permission by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Alter any connection' AND who.type_desc = 'SERVER_ROLE' ORDER BY who.name ; GO If any role has 'Grant', 'With Grant' or 'Deny' privileges on this permission and users with that role are not authorized to have the permission, this is a finding.
Remove the 'Alter any connection' permission access from the role that is not authorized by executing the following query: REVOKE Alter any connection TO <'role name'>
Obtain the list of roles that are authorized for the SQL Server 'Alter any credential' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized. Obtain the list of roles with that permission by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Alter any credential' AND who.type_desc = 'SERVER_ROLE' ORDER BY who.name ; GO If any role has 'Grant', 'With Grant' or 'Deny' privileges on this permission and users with that role are not authorized to have the permission, this is a finding.
Remove the 'Alter any credential' permission access from the role that is not authorized by executing the following query: REVOKE Alter any credential TO <'role name'>
Obtain the list of roles that are authorized for the SQL Server 'Alter any database' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized. Obtain the list of roles with that permission by running the following query: SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'Alter any database' AND who.type_desc = 'SERVER_ROLE' ORDER BY who.name ; GO If any role has 'Grant', 'With Grant' or 'Deny' privileges on this permission and users with that role are not authorized to have the permission, this is a finding.
Remove the 'Alter any database' permission access from the role that is not authorized by executing the following query: REVOKE Alter any database TO <'role name'>