Microsoft SQL Server 2012 Database Security Technical Implementation Guide
Pick two releases to diff their requirements.
Open a previous version of this STIG.
Digest of Updates ✎ 6
Comparison against the immediately-prior release (V1R9). Rule matching uses the Group Vuln ID. Content-change detection compares the rule’s description, check, and fix text after stripping inline markup — cosmetic-only edits aren’t flagged.
Content changes 6
- V-41395 Medium checkfix SQL Server must be protected from unauthorized access by developers.
- V-41411 Medium check SQL Server must encrypt information stored in the database.
- V-41412 Medium checkfix SQL Server must implement required cryptographic protections using cryptographic modules complying with applicable federal laws, Executive Orders, directives, policies, regulations, standards, and guidance.
- V-41417 Medium check Symmetric keys must use a DoD certificate to encrypt the key.
- V-41420 Medium descriptioncheckfix SQL Server must employ cryptographic mechanisms preventing the unauthorized disclosure of information at rest.
- V-41424 Medium description SQL Server must check the validity of data inputs.
- RMF Control
- AC-16
- Severity
- M
- CCI
- CCI-001399
- Version
- SQL2-00-000300
- Vuln IDs
-
- V-41389
- Rule IDs
-
- SV-53912r3_rule
Checks: C-47925r2_chk
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 settings 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. Spot check data and ensure the appropriate labels have been applied to stored data. If the labeling of sensitive data is required and is not being performed, this is a finding.
Fix: F-46813r3_fix
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.
- RMF Control
- AC-16
- Severity
- M
- CCI
- CCI-001400
- Version
- SQL2-00-000400
- Vuln IDs
-
- V-41391
- Rule IDs
-
- SV-53914r3_rule
Checks: C-47926r3_chk
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 settings 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. If the labeling of sensitive information in process is not being performed, this is a finding.
Fix: F-46814r4_fix
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.
- RMF Control
- AC-16
- Severity
- M
- CCI
- CCI-001401
- Version
- SQL2-00-000500
- Vuln IDs
-
- V-41392
- Rule IDs
-
- SV-53916r3_rule
Checks: C-47928r3_chk
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 settings 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. If the labeling of sensitive data in transmission is not being performed, this is a finding.
Fix: F-46816r3_fix
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.
- RMF Control
- AC-16
- Severity
- M
- CCI
- CCI-001427
- Version
- SQL2-00-000900
- Vuln IDs
-
- V-41393
- Rule IDs
-
- SV-53917r3_rule
Checks: C-47929r2_chk
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. Determine how authorized users associate security information to data. If authorized users are not able to associate security labels to data, this is a finding.
Fix: F-46817r3_fix
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.
- RMF Control
- AC-3
- Severity
- M
- CCI
- CCI-001693
- Version
- SQL2-00-011050
- Vuln IDs
-
- V-41394
- Rule IDs
-
- SV-53918r3_rule
Checks: C-47931r3_chk
Check for rights propagation assignment to database permissions by running the following query: USE <name of database being reviewed>; SELECT * FROM sys.database_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.
Fix: F-46818r4_fix
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 "UPDATE" with the actual permission at issue): REVOKE UPDATE ON SampleTable FROM SampleUserOrRole CASCADE; GRANT UPDATE ON SampleTable TO SampleRole; -- Note, no WITH GRANT OPTION clause here.
- RMF Control
- AC-5
- Severity
- M
- CCI
- CCI-000037
- Version
- SQL2-00-009200
- Vuln IDs
-
- V-41395
- Rule IDs
-
- SV-53920r4_rule
Checks: C-47932r4_chk
Check the list of SQL Server users against the list of developer accounts by running the following SQL Server query: SELECT name AS 'Account Name' , create_date AS 'Account Create Date' , LOGINPROPERTY(name, 'PasswordLastSetTime') AS 'Password Last Set on' FROM sys.server_principals WHERE NOT TYPE IN ('C', 'R', 'U') AND NOT name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##') AND sid <> CONVERT(VARBINARY(85), 0x01) -- no 'sa' account AND is_disabled <> 1 ORDER BY name; For each developer account found on a production machine, verify if the developer account can change or alter database objects or data in the production database. If any developer account can change or alter database objects or data in a production database, this is a finding.
Fix: F-46820r3_fix
Remove unnecessary developer accounts from SQL Server instances hosting only production databases, by running the following SQL script: USE master DROP LOGIN <'account name'>
- RMF Control
- AC-5
- Severity
- M
- CCI
- CCI-000037
- Version
- SQL2-00-009300
- Vuln IDs
-
- V-41396
- Rule IDs
-
- SV-53921r2_rule
Checks: C-47933r2_chk
Identify whether SQL Server contains both development and production databases from the system documentation. If SQL Server is not hosting both production and development databases, this is NA. If SQL Server is hosting both development and production databases, but this is not clearly documented in the system documentation, this is a finding. Check the list of SQL Server users against the list of developer accounts by running the following SQL Server query: SELECT name AS 'Account Name' , create_date AS 'Account Create Date' , LOGINPROPERTY(name, 'PasswordLastSetTime') AS 'Password Last Set on' FROM sys.server_principals WHERE NOT TYPE IN ('C', 'R', 'U') -- ('C', 'G', 'K', 'R', 'S', 'U') AND NOT name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##') AND sid <> CONVERT(VARBINARY(85), 0x01) -- no 'sa' account AND is_disabled <> 1 ORDER BY name If no developer user account is listed, this is not a finding. Check each developer user account privilege listed above. Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> right click <'developer account name'> >> Properties >> User >> Securables. If any item in the 'Permission' listing, for each highlighted item that exists in the 'Securables' listing, grants production privileges, this is a finding. Navigate from 'Securables' to 'Server Roles'. If any 'Server roles' are checked that grant production 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 granting production privileges, this is a finding.
Fix: F-46821r2_fix
Within the system documentation, clearly identify if SQL Server is hosting both development and production databases. Restrict developer privileges to production objects to only objects and data where those privileges are required and authorized by running the following scripts as needed: 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'> Remove permissions from developer user accounts that grant permissions beyond the development database. 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 accounts that are beyond what is required. Navigate from 'Securables' to 'Server Roles'. Remove 'Server Roles' permissions from accounts that are beyond what is required. Navigate from 'Server Roles' to 'Users mapped to the login'. Remove 'Users mapped to the login' permissions from accounts that are beyond what is required.
- RMF Control
- AC-5
- Severity
- M
- CCI
- CCI-000037
- Version
- SQL2-00-009500
- Vuln IDs
-
- V-41397
- Rule IDs
-
- SV-53922r5_rule
Checks: C-47934r9_chk
Check administrative accounts for direct database role membership: Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> right click <'administrator account name'> >> Properties >> User Mapping >> <'highlight database'> >> review 'Database role membership' for each database. If any administration accounts have a direct privilege to any 'Database role membership' that is part of the SQL Server system, this is a finding. Check administrative accounts for direct server role membership: Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> right click <'administrator account name'> >> Properties >> Server Roles. If any administration accounts have direct access to any 'Server roles' privilege that is part of the SQL Server system, this is a finding. The special database principal [dbo] is an exception. It is mapped to the server login that is the database owner. Some roles cannot be mapped to it or unmapped from it. These role assignments are not a finding. The built-in server role "sysadmin" is a partial exception. See the Vulnerability Discussion.
Fix: F-46822r5_fix
Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> right click <'administrator account name'> >> Properties >> User Mapping >> <'highlight the database'> >> review 'Database role membership' each database. Remove 'Database role membership' by clicking the appropriate check box. Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> right click <'administrator account name'> >> Properties >> Server Roles. Remove 'Server roles' by clicking the appropriate check box. The special database principal [dbo] is an exception. It is mapped to the server login that is the database owner. Some roles cannot be mapped to it or unmapped from it. The built-in server role "sysadmin" is a partial exception. See the Vulnerability Discussion.
- RMF Control
- CM-6
- Severity
- M
- CCI
- CCI-000366
- Version
- SQL2-00-023500
- Vuln IDs
-
- V-41399
- Rule IDs
-
- SV-53925r2_rule
Checks: C-47937r2_chk
Check system documentation for procedures that are regularly implemented in an effort to detect unauthorized SQL Server job submissions. If procedures that are regularly implemented are not documented in the system documentation, this is a finding. If the procedures are not implemented regularly or do not detect for unauthorized SQL Server job submissions, this is a finding. Review Stored Procedures that are able to automatically execute jobs scheduled to start automatically at system startup by running the following query: SELECT name FROM master.sys.procedures WHERE is_auto_executed = 1 If any Stored Procedures listed are not documented as authorized, this is a finding. Review the SQL Server job history by running the following query: SELECT * FROM msdb.dbo.sysjobhistory If any jobs listed are not documented as authorized, this is a finding.
Fix: F-46825r2_fix
Document procedures, within the system documentation, that detect for unauthorized SQL Server job submissions. Develop and implement procedures to detect for unauthorized SQL Server job submissions of Stored Procedures that are automatically executed and Agent jobs that are enabled.
- RMF Control
- AU-12
- Severity
- M
- CCI
- CCI-000169
- Version
- SQL2-00-011200
- Vuln IDs
-
- V-41402
- Rule IDs
-
- SV-53928r3_rule
Checks: C-47940r7_chk
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.
Fix: F-46828r4_fix
-- Run this script to create and start an audit trace that audits required events. -- Note: Replace 'D:<path>\<filename>' with the path and file name to your audit file. -- Adjust the other parameters of SP_TRACE_CREATE to suit your system's circumstances. -- The database server must be restarted for the trace to take effect. USE master; GO BEGIN TRY DROP PROCEDURE fso_audit END TRY BEGIN CATCH END CATCH; GO CREATE PROCEDURE fso_audit AS -- Create a Queue DECLARE @rc INT; DECLARE @TraceID INT; DECLARE @options INT = 6; -- 6 specifies TRACE_FILE_ROLLOVER (2) and SHUTDOWN_ON_ERROR (4) DECLARE @tracefile NVARCHAR(128) = 'D:<path>\<filename>'; -- Trace file location and beginning of file name (SQL Server adds a suffix) DECLARE @maxfilesize BIGINT = 500; -- Trace file size limit in megabytes DECLARE @stoptime datetime = null; -- do not stop DECLARE @filecount INT = 10; -- Number of trace files in the rollover set EXEC @rc = SP_TRACE_CREATE @TraceID output, @options, @tracefile, @maxfilesize, @stoptime, @filecount ; IF (@rc != 0) GOTO Error; -- Set the events: DECLARE @on BIT = 1; -- Logins are audited based on SQL Server instance -- setting Audit Level stored in registry -- HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.[#]\MSSQLServer\AuditLevel -- Audit Login -- Occurs when a user successfully logs in to SQL Server. EXEC SP_TRACE_SETEVENT @TraceID, 14, 1, @on; -- TextData EXEC SP_TRACE_SETEVENT @TraceID, 14, 6, @on; -- NTUserName EXEC SP_TRACE_SETEVENT @TraceID, 14, 7, @on; -- NTDomainName EXEC SP_TRACE_SETEVENT @TraceID, 14, 8, @on; -- HostName EXEC SP_TRACE_SETEVENT @TraceID, 14, 10, @on; -- ApplicationName EXEC SP_TRACE_SETEVENT @TraceID, 14, 11, @on; -- LoginName EXEC SP_TRACE_SETEVENT @TraceID, 14, 12, @on; -- SPID EXEC SP_TRACE_SETEVENT @TraceID, 14, 14, @on; -- StartTime EXEC SP_TRACE_SETEVENT @TraceID, 14, 23, @on; -- Success EXEC SP_TRACE_SETEVENT @TraceID, 14, 26, @on; -- ServerName EXEC SP_TRACE_SETEVENT @TraceID, 14, 35, @on; -- DatabaseName EXEC SP_TRACE_SETEVENT @TraceID, 14, 41, @on; -- LoginSid EXEC SP_TRACE_SETEVENT @TraceID, 14, 60, @on; -- IsSystem EXEC SP_TRACE_SETEVENT @TraceID, 14, 64, @on; -- SessionLoginName -- Audit Logout -- Occurs when a user logs out of SQL Server. EXEC SP_TRACE_SETEVENT @TraceID, 15, 6, @on; -- NTUserName EXEC SP_TRACE_SETEVENT @TraceID, 15, 7, @on; -- NTDomainName EXEC SP_TRACE_SETEVENT @TraceID, 15, 8, @on; -- HostName EXEC SP_TRACE_SETEVENT @TraceID, 15, 10, @on; -- ApplicationName EXEC SP_TRACE_SETEVENT @TraceID, 15, 11, @on; -- LoginName EXEC SP_TRACE_SETEVENT @TraceID, 15, 12, @on; -- SPID EXEC SP_TRACE_SETEVENT @TraceID, 15, 13, @on; -- Duration EXEC SP_TRACE_SETEVENT @TraceID, 15, 14, @on; -- StartTime EXEC SP_TRACE_SETEVENT @TraceID, 15, 15, @on; -- EndTime EXEC SP_TRACE_SETEVENT @TraceID, 15, 23, @on; -- Success EXEC SP_TRACE_SETEVENT @TraceID, 15, 26, @on; -- ServerName EXEC SP_TRACE_SETEVENT @TraceID, 15, 35, @on; -- DatabaseName EXEC SP_TRACE_SETEVENT @TraceID, 15, 41, @on; -- LoginSid EXEC SP_TRACE_SETEVENT @TraceID, 15, 60, @on; -- IsSystem EXEC SP_TRACE_SETEVENT @TraceID, 15, 64, @on; -- SessionLoginName -- Audit Server Starts and Stops -- Occurs when the SQL Server service state is modified. EXEC SP_TRACE_SETEVENT @TraceID, 18, 6, @on; -- NTUserName EXEC SP_TRACE_SETEVENT @TraceID, 18, 7, @on; -- NTDomainName EXEC SP_TRACE_SETEVENT @TraceID, 18, 8, @on; -- HostName EXEC SP_TRACE_SETEVENT @TraceID, 18, 10, @on; -- ApplicationName EXEC SP_TRACE_SETEVENT @TraceID, 18, 11, @on; -- LoginName EXEC SP_TRACE_SETEVENT @TraceID, 18, 12, @on; -- SPID EXEC SP_TRACE_SETEVENT @TraceID, 18, 14, @on; -- StartTime EXEC SP_TRACE_SETEVENT @TraceID, 18, 23, @on; -- Success EXEC SP_TRACE_SETEVENT @TraceID, 18, 26, @on; -- ServerName EXEC SP_TRACE_SETEVENT @TraceID, 18, 41, @on; -- LoginSid EXEC SP_TRACE_SETEVENT @TraceID, 18, 60, @on; -- IsSystem EXEC SP_TRACE_SETEVENT @TraceID, 18, 64, @on; -- SessionLoginName -- Audit Login Failed -- Indicates that a login attempt to SQL Server from a client failed. EXEC SP_TRACE_SETEVENT @TraceID, 20, 1, @on; -- TextData EXEC SP_TRACE_SETEVENT @TraceID, 20, 6, @on; -- NTUserName EXEC SP_TRACE_SETEVENT @TraceID, 20, 7, @on; -- NTDomainName EXEC SP_TRACE_SETEVENT @TraceID, 20, 8, @on; -- HostName EXEC SP_TRACE_SETEVENT @TraceID, 20, 10, @on; -- ApplicationName EXEC SP_TRACE_SETEVENT @TraceID, 20, 11, @on; -- LoginName EXEC SP_TRACE_SETEVENT @TraceID, 20, 12, @on; -- SPID EXEC SP_TRACE_SETEVENT @TraceID, 20, 14, @on; -- StartTime EXEC SP_TRACE_SETEVENT @TraceID, 20, 23, @on; -- Success EXEC SP_TRACE_SETEVENT @TraceID, 20, 26, @on; -- ServerName EXEC SP_TRACE_SETEVENT @TraceID, 20, 31, @on; -- Error EXEC SP_TRACE_SETEVENT @TraceID, 20, 35, @on; -- DatabaseName EXEC SP_TRACE_SETEVENT @TraceID, 20, 60, @on; -- IsSystem EXEC SP_TRACE_SETEVENT @TraceID, 20, 64, @on; -- SessionLoginName -- Audit Statement GDR Event -- Occurs every time a GRANT, DENY, REVOKE for a statement -- permission is issued by any user in SQL Server. EXEC SP_TRACE_SETEVENT @TraceID, 102, 1, @on; -- TextData EXEC SP_TRACE_SETEVENT @TraceID, 102, 6, @on; -- NTUserName EXEC SP_TRACE_SETEVENT @TraceID, 102, 7, @on; -- NTDomainName EXEC SP_TRACE_SETEVENT @TraceID, 102, 8, @on; -- HostName EXEC SP_TRACE_SETEVENT @TraceID, 102, 10, @on; -- ApplicationName EXEC SP_TRACE_SETEVENT @TraceID, 102, 11, @on; -- LoginName EXEC SP_TRACE_SETEVENT @TraceID, 102, 12, @on; -- SPID EXEC SP_TRACE_SETEVENT @TraceID, 102, 14, @on; -- StartTime EXEC SP_TRACE_SETEVENT @TraceID, 102, 19, @on; -- StartTime EXEC SP_TRACE_SETEVENT @TraceID, 102, 23, @on; -- Success EXEC SP_TRACE_SETEVENT @TraceID, 102, 26, @on; -- ServerName EXEC SP_TRACE_SETEVENT @TraceID, 102, 28, @on; -- ObjectType EXEC SP_TRACE_SETEVENT @TraceID, 102, 34, @on; -- ObjectName EXEC SP_TRACE_SETEVENT @TraceID, 102, 35, @on; -- DatabaseName EXEC SP_TRACE_SETEVENT @TraceID, 102, 37, @on; -- OwnerName EXEC SP_TRACE_SETEVENT @TraceID, 102, 39, @on; -- TargetUserName EXEC SP_TRACE_SETEVENT @TraceID, 102, 40, @on; -- DBUserName EXEC SP_TRACE_SETEVENT @TraceID, 102, 41, @on; -- LoginSid EXEC SP_TRACE_SETEVENT @TraceID, 102, 42, @on; -- TargetLoginName EXEC SP_TRACE_SETEVENT @TraceID, 102, 43, @on; -- TargetLoginSid EXEC SP_TRACE_SETEVENT @TraceID, 102, 60, @on; -- IsSystem EXEC SP_TRACE_SETEVENT @TraceID, 102, 64, @on; -- SessionLoginName -- Audit Object GDR Event -- Occurs every time a GRANT, DENY, REVOKE for an object -- permission is issued by any user in SQL Server. EXEC SP_TRACE_SETEVENT @TraceID, 103, 1, @on; -- TextData EXEC SP_TRACE_SETEVENT @TraceID, 103, 6, @on; -- NTUserName EXEC SP_TRACE_SETEVENT @TraceID, 103, 7, @on; -- NTDomainName EXEC SP_TRACE_SETEVENT @TraceID, 103, 8, @on; -- HostName EXEC SP_TRACE_SETEVENT @TraceID, 103, 10, @on; -- ApplicationName EXEC SP_TRACE_SETEVENT @TraceID, 103, 11, @on; -- LoginName EXEC SP_TRACE_SETEVENT @TraceID, 103, 12, @on; -- SPID EXEC SP_TRACE_SETEVENT @TraceID, 103, 14, @on; -- StartTime EXEC SP_TRACE_SETEVENT @TraceID, 103, 19, @on; -- StartTime EXEC SP_TRACE_SETEVENT @TraceID, 103, 23, @on; -- Success EXEC SP_TRACE_SETEVENT @TraceID, 103, 26, @on; -- ServerName EXEC SP_TRACE_SETEVENT @TraceID, 103, 28, @on; -- ObjectType EXEC SP_TRACE_SETEVENT @TraceID, 103, 34, @on; -- ObjectName EXEC SP_TRACE_SETEVENT @TraceID, 103, 35, @on; -- DatabaseName EXEC SP_TRACE_SETEVENT @TraceID, 103, 37, @on; -- OwnerName EXEC SP_TRACE_SETEVENT @TraceID, 103, 39, @on; -- TargetUserName EXEC SP_TRACE_SETEVENT @TraceID, 103, 40, @on; -- DBUserName EXEC SP_TRACE_SETEVENT @TraceID, 103, 41, @on; -- LoginSid EXEC SP_TRACE_SETEVENT @TraceID, 103, 42, @on; -- TargetLoginName EXEC SP_TRACE_SETEVENT @TraceID, 103, 43, @on; -- TargetLoginSid EXEC SP_TRACE_SETEVENT @TraceID, 103, 44, @on; -- ColumnPermissions EXEC SP_TRACE_SETEVENT @TraceID, 103, 59, @on; -- ParentName EXEC SP_TRACE_SETEVENT @TraceID, 103, 60, @on; -- IsSystem EXEC SP_TRACE_SETEVENT @TraceID, 103, 64, @on; -- SessionLoginName -- Audit AddLogin Event -- Occurs when a SQL Server login is added or removed; -- for sp_addlogin and sp_droplogin. EXEC SP_TRACE_SETEVENT @TraceID, 104, 6, @on; -- NTUserName EXEC SP_TRACE_SETEVENT @TraceID, 104, 7, @on; -- NTDomainName EXEC SP_TRACE_SETEVENT @TraceID, 104, 8, @on; -- HostName EXEC SP_TRACE_SETEVENT @TraceID, 104, 10, @on; -- ApplicationName EXEC SP_TRACE_SETEVENT @TraceID, 104, 11, @on; -- LoginName EXEC SP_TRACE_SETEVENT @TraceID, 104, 12, @on; -- SPID EXEC SP_TRACE_SETEVENT @TraceID, 104, 14, @on; -- StartTime EXEC SP_TRACE_SETEVENT @TraceID, 104, 23, @on; -- Success EXEC SP_TRACE_SETEVENT @TraceID, 104, 26, @on; -- ServerName EXEC SP_TRACE_SETEVENT @TraceID, 104, 35, @on; -- DatabaseName EXEC SP_TRACE_SETEVENT @TraceID, 104, 41, @on; -- LoginSid EXEC SP_TRACE_SETEVENT @TraceID, 104, 42, @on; -- TargetLoginName EXEC SP_TRACE_SETEVENT @TraceID, 104, 43, @on; -- TargetLoginSid EXEC SP_TRACE_SETEVENT @TraceID, 104, 60, @on; -- IsSystem EXEC SP_TRACE_SETEVENT @TraceID, 104, 64, @on; -- SessionLoginName -- Audit Login GDR Event -- Occurs when a Windows login right is added or removed; -- for sp_grantlogin, sp_revokelogin, and sp_denylogin. EXEC SP_TRACE_SETEVENT @TraceID, 105, 6, @on; -- NTUserName EXEC SP_TRACE_SETEVENT @TraceID, 105, 7, @on; -- NTDomainName EXEC SP_TRACE_SETEVENT @TraceID, 105, 8, @on; -- HostName EXEC SP_TRACE_SETEVENT @TraceID, 105, 10, @on; -- ApplicationName EXEC SP_TRACE_SETEVENT @TraceID, 105, 11, @on; -- LoginName EXEC SP_TRACE_SETEVENT @TraceID, 105, 12, @on; -- SPID EXEC SP_TRACE_SETEVENT @TraceID, 105, 14, @on; -- StartTime EXEC SP_TRACE_SETEVENT @TraceID, 105, 23, @on; -- Success EXEC SP_TRACE_SETEVENT @TraceID, 105, 26, @on; -- ServerName EXEC SP_TRACE_SETEVENT @TraceID, 105, 35, @on; -- DatabaseName EXEC SP_TRACE_SETEVENT @TraceID, 105, 41, @on; -- LoginSid EXEC SP_TRACE_SETEVENT @TraceID, 105, 42, @on; -- TargetLoginName EXEC SP_TRACE_SETEVENT @TraceID, 105, 43, @on; -- TargetLoginSid EXEC SP_TRACE_SETEVENT @TraceID, 105, 60, @on; -- IsSystem EXEC SP_TRACE_SETEVENT @TraceID, 105, 64, @on; -- SessionLoginName -- Audit Login Change Property Event -- Occurs when a property of a login, except passwords, -- is modified; for sp_defaultdb and sp_defaultlanguage. EXEC SP_TRACE_SETEVENT @TraceID, 106, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 106, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 106, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 106, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 106, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 106, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 106, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 106, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 106, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 106, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 106, 28, @on; EXEC SP_TRACE_SETEVENT @TraceID, 106, 34, @on; EXEC SP_TRACE_SETEVENT @TraceID, 106, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 106, 37, @on; EXEC SP_TRACE_SETEVENT @TraceID, 106, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 106, 42, @on; EXEC SP_TRACE_SETEVENT @TraceID, 106, 43, @on; EXEC SP_TRACE_SETEVENT @TraceID, 106, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 106, 64, @on; -- Audit Login Change Password Event -- Occurs when a SQL Server login password is changed. -- Passwords are not recorded. EXEC SP_TRACE_SETEVENT @TraceID, 107, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 107, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 107, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 107, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 107, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 107, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 107, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 107, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 107, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 107, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 107, 28, @on; EXEC SP_TRACE_SETEVENT @TraceID, 107, 34, @on; EXEC SP_TRACE_SETEVENT @TraceID, 107, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 107, 37, @on; EXEC SP_TRACE_SETEVENT @TraceID, 107, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 107, 42, @on; EXEC SP_TRACE_SETEVENT @TraceID, 107, 43, @on; EXEC SP_TRACE_SETEVENT @TraceID, 107, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 107, 64, @on; -- Audit Add Login to Server Role Event -- Occurs when a login is added or removed from a fixed server role; -- for sp_addsrvrolemember, and sp_dropsrvrolemember. EXEC SP_TRACE_SETEVENT @TraceID, 108, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 108, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 108, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 108, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 108, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 108, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 108, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 108, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 108, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 108, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 108, 28, @on; EXEC SP_TRACE_SETEVENT @TraceID, 108, 34, @on; EXEC SP_TRACE_SETEVENT @TraceID, 108, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 108, 37, @on; EXEC SP_TRACE_SETEVENT @TraceID, 108, 38, @on; EXEC SP_TRACE_SETEVENT @TraceID, 108, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 108, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 108, 42, @on; EXEC SP_TRACE_SETEVENT @TraceID, 108, 43, @on; EXEC SP_TRACE_SETEVENT @TraceID, 108, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 108, 64, @on; -- Audit Add DB User Event -- Occurs when a login is added or removed as a database user -- (Windows or SQL Server) to a database; for sp_grantdbaccess, -- sp_revokedbaccess, sp_adduser, and sp_dropuser. EXEC SP_TRACE_SETEVENT @TraceID, 109, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 109, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 109, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 109, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 109, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 109, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 109, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 109, 21, @on; EXEC SP_TRACE_SETEVENT @TraceID, 109, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 109, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 109, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 109, 37, @on; EXEC SP_TRACE_SETEVENT @TraceID, 109, 38, @on; EXEC SP_TRACE_SETEVENT @TraceID, 109, 39, @on; EXEC SP_TRACE_SETEVENT @TraceID, 109, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 109, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 109, 42, @on; EXEC SP_TRACE_SETEVENT @TraceID, 109, 43, @on; EXEC SP_TRACE_SETEVENT @TraceID, 109, 44, @on; EXEC SP_TRACE_SETEVENT @TraceID, 109, 51, @on; EXEC SP_TRACE_SETEVENT @TraceID, 109, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 109, 64, @on; -- Audit Add Member to DB Role Event -- Occurs when a login is added or removed as a database user -- (fixed or user-defined) to a database; for sp_addrolemember, -- sp_droprolemember, and sp_changegroup. EXEC SP_TRACE_SETEVENT @TraceID, 110, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 110, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 110, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 110, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 110, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 110, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 110, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 110, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 110, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 110, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 110, 28, @on; EXEC SP_TRACE_SETEVENT @TraceID, 110, 34, @on; EXEC SP_TRACE_SETEVENT @TraceID, 110, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 110, 37, @on; EXEC SP_TRACE_SETEVENT @TraceID, 110, 38, @on; EXEC SP_TRACE_SETEVENT @TraceID, 110, 39, @on; EXEC SP_TRACE_SETEVENT @TraceID, 110, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 110, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 110, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 110, 64, @on; -- Audit Add Role Event -- Occurs when a login is added or removed as a database user to a -- database; for sp_addrole and sp_droprole. EXEC SP_TRACE_SETEVENT @TraceID, 111, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 111, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 111, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 111, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 111, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 111, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 111, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 111, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 111, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 111, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 111, 38, @on; EXEC SP_TRACE_SETEVENT @TraceID, 111, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 111, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 111, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 111, 64, @on; -- Audit App Role Change Password Event -- Occurs when a password of an application role is changed. EXEC SP_TRACE_SETEVENT @TraceID, 112, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 112, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 112, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 112, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 112, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 112, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 112, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 112, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 112, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 112, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 112, 28, @on; EXEC SP_TRACE_SETEVENT @TraceID, 112, 34, @on; EXEC SP_TRACE_SETEVENT @TraceID, 112, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 112, 37, @on; EXEC SP_TRACE_SETEVENT @TraceID, 112, 38, @on; EXEC SP_TRACE_SETEVENT @TraceID, 112, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 112, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 112, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 112, 64, @on; -- Audit Statement Permission Event -- Occurs when a statement permission (such as CREATE TABLE) is used. EXEC SP_TRACE_SETEVENT @TraceID, 113, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 113, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 113, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 113, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 113, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 113, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 113, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 113, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 113, 19, @on; EXEC SP_TRACE_SETEVENT @TraceID, 113, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 113, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 113, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 113, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 113, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 113, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 113, 64, @on; -- Audit Backup/Restore Event -- Occurs when a BACKUP or RESTORE command is issued. EXEC SP_TRACE_SETEVENT @TraceID, 115, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 115, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 115, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 115, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 115, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 115, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 115, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 115, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 115, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 115, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 115, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 115, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 115, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 115, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 115, 64, @on; -- Audit DBCC Event -- Occurs when DBCC commands are issued. EXEC SP_TRACE_SETEVENT @TraceID, 116, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 116, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 116, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 116, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 116, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 116, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 116, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 116, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 116, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 116, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 116, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 116, 37, @on; EXEC SP_TRACE_SETEVENT @TraceID, 116, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 116, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 116, 44, @on; EXEC SP_TRACE_SETEVENT @TraceID, 116, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 116, 64, @on; -- Audit Change Audit Event -- Occurs when audit trace modifications are made. EXEC SP_TRACE_SETEVENT @TraceID, 117, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 117, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 117, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 117, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 117, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 117, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 117, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 117, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 117, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 117, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 117, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 117, 37, @on; EXEC SP_TRACE_SETEVENT @TraceID, 117, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 117, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 117, 44, @on; EXEC SP_TRACE_SETEVENT @TraceID, 117, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 117, 64, @on; -- Audit Object Derived Permission Event -- Occurs when a CREATE, ALTER, and DROP object commands are issued. EXEC SP_TRACE_SETEVENT @TraceID, 118, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 118, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 118, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 118, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 118, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 118, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 118, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 118, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 118, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 118, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 118, 28, @on; EXEC SP_TRACE_SETEVENT @TraceID, 118, 34, @on; EXEC SP_TRACE_SETEVENT @TraceID, 118, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 118, 37, @on; EXEC SP_TRACE_SETEVENT @TraceID, 118, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 118, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 118, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 118, 64, @on; -- Audit Database Management Event -- Occurs when a CREATE, ALTER, or DROP statement executes on -- database objects, such as schemas. EXEC SP_TRACE_SETEVENT @TraceID, 128, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 128, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 128, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 128, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 128, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 128, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 128, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 128, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 128, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 128, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 128, 28, @on; EXEC SP_TRACE_SETEVENT @TraceID, 128, 34, @on; EXEC SP_TRACE_SETEVENT @TraceID, 128, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 128, 37, @on; EXEC SP_TRACE_SETEVENT @TraceID, 128, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 128, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 128, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 128, 64, @on; -- Audit Database Object Management Event -- Occurs when a CREATE, ALTER, or DROP statement executes on -- database objects, such as schemas. EXEC SP_TRACE_SETEVENT @TraceID, 129, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 129, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 129, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 129, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 129, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 129, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 129, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 129, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 129, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 129, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 129, 28, @on; EXEC SP_TRACE_SETEVENT @TraceID, 129, 34, @on; EXEC SP_TRACE_SETEVENT @TraceID, 129, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 129, 37, @on; EXEC SP_TRACE_SETEVENT @TraceID, 129, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 129, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 129, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 129, 64, @on; -- Audit Database Principal Management Event -- Occurs when principals, such as users, are created, altered, or -- dropped from a database. EXEC SP_TRACE_SETEVENT @TraceID, 130, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 130, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 130, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 130, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 130, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 130, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 130, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 130, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 130, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 130, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 130, 28, @on; EXEC SP_TRACE_SETEVENT @TraceID, 130, 34, @on; EXEC SP_TRACE_SETEVENT @TraceID, 130, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 130, 37, @on; EXEC SP_TRACE_SETEVENT @TraceID, 130, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 130, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 130, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 130, 64, @on; -- Audit Schema Object Management Event -- Occurs when server objects are created, altered, or dropped. EXEC SP_TRACE_SETEVENT @TraceID, 131, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 131, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 131, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 131, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 131, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 131, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 131, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 131, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 131, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 131, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 131, 28, @on; EXEC SP_TRACE_SETEVENT @TraceID, 131, 34, @on; EXEC SP_TRACE_SETEVENT @TraceID, 131, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 131, 37, @on; EXEC SP_TRACE_SETEVENT @TraceID, 131, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 131, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 131, 59, @on; EXEC SP_TRACE_SETEVENT @TraceID, 131, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 131, 64, @on; -- Audit Server Principal Impersonation Event -- Occurs when there is an impersonation within server scope, such -- as EXECUTE AS LOGIN. EXEC SP_TRACE_SETEVENT @TraceID, 132, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 132, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 132, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 132, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 132, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 132, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 132, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 132, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 132, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 132, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 132, 28, @on; EXEC SP_TRACE_SETEVENT @TraceID, 132, 34, @on; EXEC SP_TRACE_SETEVENT @TraceID, 132, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 132, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 132, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 132, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 132, 64, @on; -- Audit Database Principal Impersonation Event -- Occurs when an impersonation occurs within the database scope, -- such as EXECUTE AS USER or SETUSER. EXEC SP_TRACE_SETEVENT @TraceID, 133, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 133, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 133, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 133, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 133, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 133, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 133, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 133, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 133, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 133, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 133, 28, @on; EXEC SP_TRACE_SETEVENT @TraceID, 133, 34, @on; EXEC SP_TRACE_SETEVENT @TraceID, 133, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 133, 38, @on; EXEC SP_TRACE_SETEVENT @TraceID, 133, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 133, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 133, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 133, 64, @on; -- Audit Server Object Take Ownership Event -- Occurs when the owner is changed for objects in server scope. EXEC SP_TRACE_SETEVENT @TraceID, 134, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 134, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 134, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 134, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 134, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 134, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 134, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 134, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 134, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 134, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 134, 28, @on; EXEC SP_TRACE_SETEVENT @TraceID, 134, 34, @on; EXEC SP_TRACE_SETEVENT @TraceID, 134, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 134, 37, @on; EXEC SP_TRACE_SETEVENT @TraceID, 134, 39, @on; EXEC SP_TRACE_SETEVENT @TraceID, 134, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 134, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 134, 42, @on; EXEC SP_TRACE_SETEVENT @TraceID, 134, 43, @on; EXEC SP_TRACE_SETEVENT @TraceID, 134, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 134, 64, @on; -- Audit Database Object Take Ownership Event -- Occurs when a change of owner for objects within database scope -- occurs. EXEC SP_TRACE_SETEVENT @TraceID, 135, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 135, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 135, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 135, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 135, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 135, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 135, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 135, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 135, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 135, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 135, 28, @on; EXEC SP_TRACE_SETEVENT @TraceID, 135, 34, @on; EXEC SP_TRACE_SETEVENT @TraceID, 135, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 135, 37, @on; EXEC SP_TRACE_SETEVENT @TraceID, 135, 39, @on; EXEC SP_TRACE_SETEVENT @TraceID, 135, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 135, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 135, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 135, 64, @on; -- Audit Change Database Owner -- Occurs when ALTER AUTHORIZATION is used to change the owner of a -- database and permissions are checked to do that. EXEC SP_TRACE_SETEVENT @TraceID, 152, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 152, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 152, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 152, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 152, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 152, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 152, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 152, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 152, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 152, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 152, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 152, 39, @on; EXEC SP_TRACE_SETEVENT @TraceID, 152, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 152, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 152, 42, @on; EXEC SP_TRACE_SETEVENT @TraceID, 152, 43, @on; EXEC SP_TRACE_SETEVENT @TraceID, 152, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 152, 64, @on; -- Audit Schema Object Take Ownership Event -- Occurs when ALTER AUTHORIZATION is used to assign an owner to an -- object and permissions are checked to do that. EXEC SP_TRACE_SETEVENT @TraceID, 153, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 153, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 153, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 153, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 153, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 153, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 153, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 153, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 153, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 153, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 153, 28, @on; EXEC SP_TRACE_SETEVENT @TraceID, 153, 34, @on; EXEC SP_TRACE_SETEVENT @TraceID, 153, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 153, 37, @on; EXEC SP_TRACE_SETEVENT @TraceID, 153, 39, @on; EXEC SP_TRACE_SETEVENT @TraceID, 153, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 153, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 153, 59, @on; EXEC SP_TRACE_SETEVENT @TraceID, 153, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 153, 64, @on; -- Audit Server Scope GDR Event -- Indicates that a grant, deny, or revoke event for permissions in -- server scope occurred, such as creating a login. EXEC SP_TRACE_SETEVENT @TraceID, 170, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 170, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 170, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 170, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 170, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 170, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 170, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 170, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 170, 19, @on; EXEC SP_TRACE_SETEVENT @TraceID, 170, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 170, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 170, 28, @on; EXEC SP_TRACE_SETEVENT @TraceID, 170, 34, @on; EXEC SP_TRACE_SETEVENT @TraceID, 170, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 170, 37, @on; EXEC SP_TRACE_SETEVENT @TraceID, 170, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 170, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 170, 42, @on; EXEC SP_TRACE_SETEVENT @TraceID, 170, 43, @on; EXEC SP_TRACE_SETEVENT @TraceID, 170, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 170, 64, @on; -- Audit Server Object GDR Event -- Indicates that a grant, deny, or revoke event for a schema object, -- such as a table or function, occurred. EXEC SP_TRACE_SETEVENT @TraceID, 171, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 171, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 171, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 171, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 171, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 171, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 171, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 171, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 171, 19, @on; EXEC SP_TRACE_SETEVENT @TraceID, 171, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 171, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 171, 28, @on; EXEC SP_TRACE_SETEVENT @TraceID, 171, 34, @on; EXEC SP_TRACE_SETEVENT @TraceID, 171, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 171, 37, @on; EXEC SP_TRACE_SETEVENT @TraceID, 171, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 171, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 171, 42, @on; EXEC SP_TRACE_SETEVENT @TraceID, 171, 43, @on; EXEC SP_TRACE_SETEVENT @TraceID, 171, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 171, 64, @on; -- Audit Database Object GDR Event -- Indicates that a grant, deny, or revoke event for database -- objects, such as assemblies and schemas, occurred. EXEC SP_TRACE_SETEVENT @TraceID, 172, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 172, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 172, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 172, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 172, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 172, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 172, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 172, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 172, 19, @on; EXEC SP_TRACE_SETEVENT @TraceID, 172, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 172, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 172, 28, @on; EXEC SP_TRACE_SETEVENT @TraceID, 172, 34, @on; EXEC SP_TRACE_SETEVENT @TraceID, 172, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 172, 37, @on; EXEC SP_TRACE_SETEVENT @TraceID, 172, 39, @on; EXEC SP_TRACE_SETEVENT @TraceID, 172, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 172, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 172, 42, @on; EXEC SP_TRACE_SETEVENT @TraceID, 172, 43, @on; EXEC SP_TRACE_SETEVENT @TraceID, 172, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 172, 64, @on; -- Audit Server Operation Event -- Occurs when Security Audit operations such as altering settings, -- resources, external access, or authorization are used. EXEC SP_TRACE_SETEVENT @TraceID, 173, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 173, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 173, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 173, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 173, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 173, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 173, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 173, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 173, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 173, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 173, 28, @on; EXEC SP_TRACE_SETEVENT @TraceID, 173, 34, @on; EXEC SP_TRACE_SETEVENT @TraceID, 173, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 173, 37, @on; EXEC SP_TRACE_SETEVENT @TraceID, 173, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 173, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 173, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 173, 64, @on; -- Audit Server Alter Trace Event -- Occurs when a statement checks for the ALTER TRACE permission. EXEC SP_TRACE_SETEVENT @TraceID, 175, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 175, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 175, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 175, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 175, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 175, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 175, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 175, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 175, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 175, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 175, 28, @on; EXEC SP_TRACE_SETEVENT @TraceID, 175, 34, @on; EXEC SP_TRACE_SETEVENT @TraceID, 175, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 175, 37, @on; EXEC SP_TRACE_SETEVENT @TraceID, 175, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 175, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 175, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 175, 64, @on; -- Audit Server Object Management Event -- Occurs when server objects are created, altered, or dropped. EXEC SP_TRACE_SETEVENT @TraceID, 176, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 176, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 176, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 176, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 176, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 176, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 176, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 176, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 176, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 176, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 176, 28, @on; EXEC SP_TRACE_SETEVENT @TraceID, 176, 34, @on; EXEC SP_TRACE_SETEVENT @TraceID, 176, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 176, 37, @on; EXEC SP_TRACE_SETEVENT @TraceID, 176, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 176, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 176, 45, @on; EXEC SP_TRACE_SETEVENT @TraceID, 176, 46, @on; EXEC SP_TRACE_SETEVENT @TraceID, 176, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 176, 64, @on; -- Audit Server Principal Management Event -- Occurs when server principals are created, altered, or dropped. EXEC SP_TRACE_SETEVENT @TraceID, 177, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 177, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 177, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 177, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 177, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 177, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 177, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 177, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 177, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 177, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 177, 28, @on; EXEC SP_TRACE_SETEVENT @TraceID, 177, 34, @on; EXEC SP_TRACE_SETEVENT @TraceID, 177, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 177, 37, @on; EXEC SP_TRACE_SETEVENT @TraceID, 177, 39, @on; EXEC SP_TRACE_SETEVENT @TraceID, 177, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 177, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 177, 42, @on; EXEC SP_TRACE_SETEVENT @TraceID, 177, 43, @on; EXEC SP_TRACE_SETEVENT @TraceID, 177, 45, @on; EXEC SP_TRACE_SETEVENT @TraceID, 177, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 177, 64, @on; -- Audit Database Operation Event -- Occurs when database operations occur, such as checkpoint or -- subscribe query notification. EXEC SP_TRACE_SETEVENT @TraceID, 178, 1, @on; EXEC SP_TRACE_SETEVENT @TraceID, 178, 6, @on; EXEC SP_TRACE_SETEVENT @TraceID, 178, 7, @on; EXEC SP_TRACE_SETEVENT @TraceID, 178, 8, @on; EXEC SP_TRACE_SETEVENT @TraceID, 178, 10, @on; EXEC SP_TRACE_SETEVENT @TraceID, 178, 11, @on; EXEC SP_TRACE_SETEVENT @TraceID, 178, 12, @on; EXEC SP_TRACE_SETEVENT @TraceID, 178, 14, @on; EXEC SP_TRACE_SETEVENT @TraceID, 178, 23, @on; EXEC SP_TRACE_SETEVENT @TraceID, 178, 26, @on; EXEC SP_TRACE_SETEVENT @TraceID, 178, 28, @on; EXEC SP_TRACE_SETEVENT @TraceID, 178, 34, @on; EXEC SP_TRACE_SETEVENT @TraceID, 178, 35, @on; EXEC SP_TRACE_SETEVENT @TraceID, 178, 37, @on; EXEC SP_TRACE_SETEVENT @TraceID, 178, 40, @on; EXEC SP_TRACE_SETEVENT @TraceID, 178, 41, @on; EXEC SP_TRACE_SETEVENT @TraceID, 178, 60, @on; EXEC SP_TRACE_SETEVENT @TraceID, 178, 64, @on; -- Set the trace status to start. EXEC SP_TRACE_SETSTATUS @TraceID, 1; -- Display trace ID for future reference. SELECT @TraceID AS TraceID; GOTO Finish; Error: SELECT @rc AS ErrorCode; Finish: GO EXEC SP_PROCOPTION 'fso_audit', 'startup', 'true'; GO -- Note: Replace 'D:<path>\<filename>' with the path and file name to your audit file. -- Adjust the other parameters of SP_TRACE_CREATE to suit your system's circumstances.
- RMF Control
- CM-5
- Severity
- M
- CCI
- CCI-001499
- Version
- SQL2-00-014900
- Vuln IDs
-
- V-41403
- Rule IDs
-
- SV-53930r3_rule
Checks: C-47941r3_chk
Check the SQL Server configuration for a timed job that automatically checks all system and user-defined Functions for being modified by running the following SQL Server query: EXEC msdb.dbo.sp_help_job @job_name = '<enter . . . job name>'; (Alternatively, in SQL Server Management Studio, navigate to SQL Server Agent and examine the job from there.) If a timed job or some other method is not implemented to check for Functions being modified, this is a finding.
Fix: F-46831r1_fix
Configure a SQL Server timed job that automatically checks all system and user-defined Functions for being modified. If any Function is modified, an alert must be sent to the proper personnel for evaluation and possible action.
- RMF Control
- CM-5
- Severity
- M
- CCI
- CCI-001499
- Version
- SQL2-00-015100
- Vuln IDs
-
- V-41404
- Rule IDs
-
- SV-53931r3_rule
Checks: C-47942r3_chk
Check the SQL Server configuration for the timed job that automatically checks all system and user-defined Triggers for being modified by running the following SQL Server query: EXEC msdb.dbo.sp_help_job @job_name = '<enter . . . job name>'; (Alternatively, in SQL Server Management Studio, navigate to SQL Server Agent and examine the job from there.) If such a job, or an alternative method of monitoring triggers for modification, does not exist, this is a finding.
Fix: F-46832r1_fix
Configure a SQL Server timed job that automatically checks all system and user-defined Triggers for modification.
- RMF Control
- CM-5
- Severity
- M
- CCI
- CCI-001499
- Version
- SQL2-00-015200
- Vuln IDs
-
- V-41406
- Rule IDs
-
- SV-53933r3_rule
Checks: C-47943r5_chk
Check for the existence of a job to monitor for changes to stored procedures: EXEC msdb.dbo.sp_help_job @job_name = '<enter . . . job name>'; (Alternatively, in SQL Server Management Studio, navigate to SQL Server Agent and examine the job from there.) If such a job, or an alternative method of monitoring stored procedures for modification, does not exist, this is a finding.
Fix: F-46833r1_fix
Configure a SQL Server timed job that automatically checks all system and user-defined Stored Procedures for modification.
- RMF Control
- CM-5
- Severity
- M
- CCI
- CCI-001499
- Version
- SQL2-00-015600
- Vuln IDs
-
- V-41407
- Rule IDs
-
- SV-53935r2_rule
Checks: C-47945r2_chk
Review system documentation to identify SQL Server accounts authorized to own database objects. If the SQL Server database ownership list does not exist or needs to be updated, this is a finding. Run the following SQL query to determine SQL Server ownership of all database objects: SELECT name AS 'Database name' , SUSER_SNAME(owner_sid) AS 'Database Owner' , state_desc AS 'Database state' FROM sys.databases
Fix: F-46835r2_fix
Add and/or update system documentation to include any accounts authorized for object ownership and remove any account not authorized. Reassign database ownership to authorized database owner account: Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Databases >> right click <'database name'> >> Properties >> Files. Select new database "Owner": Navigate to click on […] >> Select new Database Owner >> Browse… >> click on box to indicate account >> <'OK'> >> <'OK'> >> <'OK'>
- RMF Control
- CM-7
- Severity
- M
- CCI
- CCI-000381
- Version
- SQL2-00-016900
- Vuln IDs
-
- V-41409
- Rule IDs
-
- SV-53937r2_rule
Checks: C-47946r2_chk
Review the list of components or optional features installed with SQL Server. If optional features or components of SQL Server are installed, this is not a finding. If unused components or features of SQL Server are installed, then review the system documentation to verify unused components or features are documented and authorized. If any are not documented and authorized, this is a finding.
Fix: F-46837r2_fix
If any database components or objects 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 objects of SQL Server are installed, uninstall or remove unused components or objects.
- RMF Control
- MP-4
- Severity
- M
- CCI
- CCI-001019
- Version
- SQL2-00-019300
- Vuln IDs
-
- V-41411
- Rule IDs
-
- SV-53939r4_rule
Checks: C-47947r4_chk
Review SQL Server's cryptographic settings to determine whether data stored in databases is encrypted according to organizational requirements and the system owner. If all of the data on SQL Server is unclassified and encryption of information is not required, this requirement is NA. Ensure the data is encrypted by executing: USE <databse name>; IF NOT EXISTS ( SELECT 1 FROM sys.dm_database_encryption_keys WHERE DB_NAME(database_id) = DB_NAME() ) SELECT DB_NAME() AS [Database Name], 'No database encryption key present, no encryption' AS [Encryption State] ELSE SELECT DB_NAME(database_id) AS [Database Name], CASE 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 WHERE DB_NAME(database_id) = DB_NAME() ; If any database that is supposed to have encryption enabled is not listed as such, this is a finding. If encryption is required by the information owner and an approved, NIST-certified cryptography is not used to encrypt stored sensitive information, this is a finding. Verify all sensitive information is encrypted: entire database, tables, columns and/or data elements, as required by the organization and the system owner.
Fix: F-46838r4_fix
Use third-party tools or configure SQL Server to encrypt data stored in the database. Use only NIST-certified or NSA-approved cryptography to provide encryption. 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 name'> Set SQL Server configuration settings to encrypt databases, tables, columns, and/or data elements as required by the organization and the system owner. Document all instances of acceptance of risk by the information owner where sensitive or classified data is not encrypted. Have the ISSO document assurance that the unencrypted sensitive or classified information is otherwise inaccessible to those who do not have need-to-know access to the data. Developers should consider using a record-specific encryption method to protect individual records. For example, by employing the session username or other individualized element as part of the encryption key, then decryption of a data element is only possible by that user or other data accessible only by that user. Data labeling can be helpful in implementation. Consider applying additional auditing of access to any unencrypted sensitive or classified data when accessed by users (with and/or without a need to know).
- RMF Control
- SC-13
- Severity
- M
- CCI
- CCI-001144
- Version
- SQL2-00-019500
- Vuln IDs
-
- V-41412
- Rule IDs
-
- SV-53940r5_rule
Checks: C-47949r6_chk
If encryption is not required for this database, this is not a finding. Run the following SQL queries to review SQL Server's cryptographic settings for the database: USE <database name> ; IF NOT EXISTS ( SELECT 1 FROM sys.dm_database_encryption_keys WHERE DB_NAME(database_id) = DB_NAME() ) SELECT DB_NAME() AS [Database Name], 'No database encryption key present, no encryption' AS [Encryption State], NULL AS [Algorithm], NULL AS [Key Length] ELSE SELECT DB_NAME(database_id) AS [Database Name], CASE 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], key_algorithm AS [Algorithm], key_length AS [Key Length] FROM sys.dm_database_encryption_keys WHERE DB_NAME(database_id) = DB_NAME() SELECT DB_NAME() AS [Database], name, algorithm_desc FROM sys.symmetric_keys ORDER BY name, algorithm_desc; Note: The acceptable algorithms are: "AES 128", "AES 192", "AES 256" and "Triple DES". If SQL Server cryptographic algorithms are not listed or are found not to be compliant with applicable federal laws, Executive Orders, directives, policies, regulations, standards and guidance, this is a finding. If the encryption state indicates that the database is unencrypted, this is a finding.
Fix: F-46839r9_fix
Implement required cryptographic protections using cryptographic modules complying with applicable federal laws, Executive Orders, directives, policies, regulations, standards, and guidance. Ensure the database is backed up. Run the following SQL to undo encryption and drop the existing database encryption key: USE master; GO ALTER DATABASE <database name> SET ENCRYPTION OFF; GO USE <database name> ; GO DROP DATABASE ENCRYPTION KEY; GO Run the following SQL to drop a server certificate from the SQL Server instance: USE master; GO DROP CERTIFICATE <certificate name>; GO If applicable, run the following SQL to drop a symmetric key: USE <database name>; GO DROP SYMMETRIC KEY <key name>; GO Configure encryption to use approved encryption algorithms. Existing keys are not reconfigurable to use different algorithms. Run SQL along the lines of the following to import an externally-created server certificate (see Microsoft documentation for options and syntax details): USE master; GO CREATE CERTIFICATE <certificate name> FROM FILE = '<path\file_name>' ... ; GO Run the following SQL to create a database encryption key and encrypt the database: USE <database name>; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE <certificate name>; GO USE master; GO ALTER DATABASE <database name> SET ENCRYPTION ON; GO Note: The acceptable algorithms are: "AES 128", "AES 192", "AES 256" and "Triple DES". If required, run the following SQL to create a symmetric key and assign an existing certificate: USE <database name>; GO CREATE SYMMETRIC KEY <key name> WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE <certificate name>;
- RMF Control
- SC-28
- Severity
- M
- CCI
- CCI-001199
- Version
- SQL2-00-024000
- Vuln IDs
-
- V-41413
- Rule IDs
-
- SV-53942r3_rule
Checks: C-47950r4_chk
From the query prompt: SELECT name FROM [master].sys.databases WHERE state = 0 Repeat for each database: From the query prompt: USE [database name] SELECT COUNT(name) FROM sys.symmetric_keys s, sys.key_encryptions k WHERE s.name = '##MS_DatabaseMasterKey##' AND s.symmetric_key_id = k.key_id AND k.crypt_type = 'ESKP' If the value returned is greater than 0, a Database Master Key exists and is encrypted with a password. Review procedures and evidence of password requirements used to encrypt Database Master Keys. If the passwords are not required to meet DoD password standards, currently a minimum of 15 characters with at least 1 upper-case character, 1 lower-case character, 1 special character and 1 numeric character, and at least 8 characters changed from the previous password, this is a finding.
Fix: F-46842r5_fix
Assign an encryption password to the Database Master Key that is a minimum of 15 characters with at least 1 upper-case character, 1 lower-case character, 1 special character and 1 numeric character, and at least 8 characters changed from the previous password. To change the Database Master Key encryption password: USE [database name] ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '[new password]' Note: The Database Master Key encryption method should not be changed until the effects are thoroughly reviewed. Changing the master key encryption causes all encryption using the Database Master Key to be decrypted and re-encrypted. This action should not be taken during a high-demand time. Please see the MS SQL Server documentation prior to re-encrypting the Database Master Key for detailed information.
- RMF Control
- SC-28
- Severity
- M
- CCI
- CCI-001199
- Version
- SQL2-00-024100
- Vuln IDs
-
- V-41415
- Rule IDs
-
- SV-53944r3_rule
Checks: C-47951r2_chk
From the query prompt: SELECT name FROM [master].sys.databases WHERE is_master_key_encrypted_by_server = 1 AND owner_sid <> 1 AND state = 0 If no databases are returned, this is not a finding. For any databases returned, verify in the System Security Plan that encryption of the Database Master Key using the Service Master Key is acceptable and approved by the Information Owner, and the encrypted data does not require additional protections to deter or detect DBA access. If not approved, this is a finding. If approved and additional protections are required, then verify the additional requirements are in place in accordance with the System Security Plan. These may include additional auditing on access of the Database Master Key with alerts or other automated monitoring. If the additional requirements are not in place, this is a finding.
Fix: F-46843r2_fix
Where possible, encrypt the Database Master Key with a password known only to the application administrator. Where not possible, configure additional audit events or alerts to detect unauthorized access to the Database Master Key by users not authorized to view sensitive data.
- RMF Control
- SC-28
- Severity
- M
- CCI
- CCI-001199
- Version
- SQL2-00-024200
- Vuln IDs
-
- V-41416
- Rule IDs
-
- SV-53945r2_rule
Checks: C-47952r1_chk
From the query prompt: SELECT COUNT(credential_id) FROM [master].sys.master_key_passwords If count is not 0, this is a finding.
Fix: F-46845r2_fix
Use the stored procedure sp_control_dbmasterkey_password to remove any credentials that store Database Master Key passwords. From the query prompt: EXEC SP_CONTROL_DBMASTERKEY_PASSWORD @db_name = '[database name]', @action = N'drop'
- RMF Control
- SC-28
- Severity
- M
- CCI
- CCI-001199
- Version
- SQL2-00-024300
- Vuln IDs
-
- V-41417
- Rule IDs
-
- SV-53946r4_rule
Checks: C-47953r5_chk
Run the query: USE [database name] SELECT s.name, k.crypt_type_desc FROM sys.symmetric_keys s, sys.key_encryptions k WHERE s.symmetric_key_id = k.key_id AND k.crypt_type IN ('ESKP', 'ESKS') ORDER BY s.name, k.crypt_type_desc Review any symmetric keys that have been defined against the System Security Plan. If any keys are defined that are not documented in the System Security Plan, this is a finding. Review the System Security Plan to review the encryption mechanism specified for each symmetric key. If the method does not indicate use of certificates, this is a finding. If the certificate specified is not a DoD PKI certificate, this is a finding.
Fix: F-46846r2_fix
Configure or alter symmetric keys to encrypt keys with certificates or authorized asymmetric keys: From the query prompt: ALTER SYMMETRIC KEY [key name] ADD ENCRYPTION BY CERTIFICATE [certificate name] ALTER SYMMETRIC KEY [key name] DROP ENCRYPTION BY [password, symmetric key or asymmetric key] The symmetric key must specify a certificate or asymmetric key for encryption.
- RMF Control
- SC-28
- Severity
- M
- CCI
- CCI-001199
- Version
- SQL2-00-024500
- Vuln IDs
-
- V-41419
- Rule IDs
-
- SV-53948r2_rule
Checks: C-47954r2_chk
Review procedures for, and evidence of backup of, the SQL Server Service Master Key in the System Security Plan. If the procedures or evidence does not exist, this is a finding. If the procedures do not indicate offline and off-site storage of the Service Master Key, this is a finding. If procedures do not indicate access restrictions to the Service Master Key backup, this is a finding.
Fix: F-46847r2_fix
Document and implement procedures to safely back up and store the Service Master Key. Include in the procedures methods to establish evidence of backup and storage, and careful, restricted access and restoration of the Service Master Key. Also, include provisions to store the key off-site. BACKUP SERVICE MASTER KEY TO FILE = 'path_to_file' ENCRYPTION BY PASSWORD = 'password'
- RMF Control
- SC-28
- Severity
- M
- CCI
- CCI-001200
- Version
- SQL2-00-021400
- Vuln IDs
-
- V-41420
- Rule IDs
-
- SV-53949r4_rule
Checks: C-47955r5_chk
Ensure the data is encrypted by executing: USE <database name>; IF NOT EXISTS ( SELECT 1 FROM sys.dm_database_encryption_keys WHERE DB_NAME(database_id) = DB_NAME() ) SELECT DB_NAME() AS [Database Name], 'No database encryption key present, no encryption' AS [Encryption State] ELSE SELECT DB_NAME(database_id) AS [Database Name], CASE 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 WHERE DB_NAME(database_id) = DB_NAME() ; For each user database, ensure that encryption is in effect. If not, this is a finding.
Fix: F-46848r3_fix
Use encryption to protect the data. To enable database encryption, create a master key, create a database encryption key, and protect it by using mechanisms tied to the master key, and then set encryption on.
- RMF Control
- SC-4
- Severity
- M
- CCI
- CCI-001090
- Version
- SQL2-00-021800
- Vuln IDs
-
- V-41421
- Rule IDs
-
- SV-53950r2_rule
Checks: C-47956r2_chk
Verify there are proper procedures in place for the transfer of development/test data from production. Review any scripts or code that exists for the movement of production data to development/test and verify copies of production data are not left in unprotected locations. If there is no documented procedure for data movement from production to development/test, this is a finding. If data movement code that copies from production to development/test does exist and leaves any copies of production data in unprotected locations, this is a finding.
Fix: F-46850r2_fix
Create and document a process for moving data from production to development/test systems and follow the process. Modify any code used for moving data from production to development/test systems to ensure copies of production data are not left in unsecured locations.
- RMF Control
- SC-5
- Severity
- M
- CCI
- CCI-001092
- Version
- SQL2-00-022000
- Vuln IDs
-
- V-41422
- Rule IDs
-
- SV-53951r2_rule
Checks: C-47957r2_chk
Check SQL Server settings for the number of concurrent Check SQL Server settings for the number of concurrent sessions by running the following script: USE MASTER GO EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'user connections' EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE GO If SQL Server settings for concurrent sessions is not lower than or equal to the organization-defined maximum number of sessions, this is a finding.
Fix: F-46851r2_fix
Configure SQL Server number of concurrent sessions to the organization-defined maximum number of sessions by running the following script: USE MASTER GO EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'user connections', <'maximum number of SQL Server concurrent connections'> EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE GO
- RMF Control
- SI-10
- Severity
- M
- CCI
- CCI-001310
- Version
- SQL2-00-022500
- Vuln IDs
-
- V-41424
- Rule IDs
-
- SV-53953r3_rule
Checks: C-47958r2_chk
Review SQL Server field definitions, constraints, and foreign keys to determine whether or not data being input into the database is valid. If field definitions are not reflective of the data, this is a finding. If column data types are not assigned correctly where required within the database, this is a finding. If columns do not contain reasonable constraints based on column use, this is a finding.
Fix: F-46852r2_fix
Use triggers, constraints, foreign keys, etc. to validate data input. Modify SQL Server to properly use the correct column data types as required in the database.
- RMF Control
- CM-5
- Severity
- M
- CCI
- CCI-001499
- Version
- SQL2-00-015620
- Vuln IDs
-
- V-60671
- Rule IDs
-
- SV-75113r1_rule
Checks: C-61703r3_chk
If the database is owned by an account that is directly or indirectly a member of a fixed (built-in) server role, this is not applicable (NA). Run the query: USE <database name>; GO SELECT DB_NAME() AS [Database], SUSER_SNAME(D.owner_sid) AS [Database Owner], CASE WHEN D.is_trustworthy_on = 1 THEN 'ON' ELSE 'off' END AS [Trustworthy] FROM sys.databases D WHERE D.[name] = DB_NAME() AND DB_NAME() <> 'msdb' AND D.is_trustworthy_on = 1; GO If the query returns a row indicating that the TRUSTWORTHY setting is OFF, or returns no rows, this is not a finding. Review the system security plan to determine whether the need for TRUSTWORTHY is documented and approved. If not, this is a finding.
Fix: F-66461r1_fix
USE [master]; GO ALTER DATABASE <name> SET TRUSTWORTHY OFF; GO
- RMF Control
- CM-5
- Severity
- M
- CCI
- CCI-001499
- Version
- SQL2-00-015610
- Vuln IDs
-
- V-60781
- Rule IDs
-
- SV-75233r1_rule
Checks: C-61705r2_chk
USE <database name>; GO WITH FixedServerRoles(RoleName) AS ( SELECT 'sysadmin' UNION SELECT 'securityadmin' UNION SELECT 'serveradmin' UNION SELECT 'setupadmin' UNION SELECT 'processadmin' UNION SELECT 'diskadmin' UNION SELECT 'dbcreator' UNION SELECT 'bulkadmin' ) SELECT DB_NAME() AS [Database], SUSER_SNAME(D.owner_sid) AS [Database Owner], F.RoleName AS [Fixed Server Role], CASE WHEN D.is_trustworthy_on = 1 THEN 'ON' ELSE 'off' END AS [Trustworthy] FROM FixedServerRoles F INNER JOIN sys.databases D ON D.Name = DB_NAME() WHERE IS_SRVROLEMEMBER(F.RoleName, SUSER_SNAME(D.owner_sid)) = 1 AND DB_NAME() <> 'msdb' AND D.is_trustworthy_on = 1; GO If the query returns any rows, this is a finding.
Fix: F-66463r5_fix
Set the TRUSTWORTHY property OFF; or remove the database owner from the fixed server role(s); or change the database owner. To set the TRUSTWORTHY property OFF: USE [master]; GO ALTER DATABASE <name> SET TRUSTWORTHY OFF; GO Verify that this produced the intended result by re-running the query specified in the Check. To determine the path or paths by which the database owner is assigned the fixed server role or roles, run this query: USE <database name>; GO WITH C AS ( SELECT P.name AS [Parent Server Role], CAST('Fixed' AS varchar(8)) AS [Server Role Type], M.name AS [Member], M.type_desc AS [Member Type], P.name AS [Root], 1 AS [Level] FROM [sys].[server_role_members] X INNER JOIN [sys].[server_principals] P ON P.principal_id = X.role_principal_id INNER JOIN [sys].[server_principals] M ON M.principal_id = X.member_principal_id WHERE P.is_fixed_role = 1 UNION ALL SELECT P.name AS [Parent Server Role], CASE WHEN M.is_fixed_role = 1 THEN CAST('Fixed' AS varchar(8)) ELSE CAST('Custom' AS varchar(8)) END AS [Server Role Type], M.name AS [Member], M.type_desc AS [Member Type], C.[Root] AS [Root], C.[Level] + 1 AS [Level] FROM [sys].[server_role_members] X INNER JOIN [sys].[server_principals] P ON P.principal_id = X.role_principal_id INNER JOIN [sys].[server_principals] M ON M.principal_id = X.member_principal_id INNER JOIN C ON P.name = C.Member ) , B AS ( SELECT C.[Member] AS [Leaf], C.[Root], C.[Parent Server Role], C.[Server Role Type], C.[Member], C.[Member Type], C.[Level] FROM C WHERE C.[Member Type] NOT LIKE '%ROLE%' UNION ALL SELECT B.[Leaf], C.[Root], C.[Parent Server Role], C.[Server Role Type], C.[Member], C.[Member Type], C.[Level] FROM C INNER JOIN B ON C.[Member] = B.[Parent Server Role] AND C.[Level] = B.[Level] - 1 AND C.[Root] = B.[Root] ) SELECT DB_NAME() AS [Database], B.[Leaf] AS [Owner Login], B.[Root] AS[Top-Level Server Role], B.[Parent Server Role], B.[Server Role Type], B.[Member], B.[Member Type], B.[Level] FROM B WHERE B.[Leaf] = (SELECT SUSER_SNAME(D.owner_sid) FROM sys.databases D WHERE D.Name = DB_NAME()) ORDER BY B.[Root], B.[Level], B.[Parent Server Role], B.[Member] ; GO To remove the database owner from a fixed server role or a custom server role: USE [master]; GO ALTER SERVER ROLE <fixed/custom server role name> DROP MEMBER <database owner name>; GO Verify that this produced the intended result by re-running the Check query. To change the database owner: USE [master]; GO ALTER AUTHORIZATION ON DATABASE::<DB name> TO <new owner name>; GO Verify that this produced the intended result by re-running the Check query.