Cyber Monday Deal : Flat 30% OFF! + free self-paced courses - SCHEDULE CALL
Ans: Creating a database audit specification recorded in the event log can be achieved using the clause WITH (STATE=ON
). The CREATE DATABASE AUDIT SPECIFICATION and ALTER DATABASE AUDIT SPECIFICATION statements function similarly to their counterparts for server audit specifications, as you've already learned.
Around 24 action groups at the database level, such as changes in ownership or permissions, can be monitored for auditing purposes. The comprehensive list is available in the SQL Server Audit Action Groups and Actions article, accessible at http://technet.microsoft.com/en-us/library/cc280663(v=sql.110).aspx. Additionally, specific actions on database objects like schemas, tables, views, stored procedures, etc., can be tracked. The seven database-level audit actions are SELECT, INSERT, UPDATE, DELETE, EXECUTE, RECEIVE, and REFERENCES.
-- Create an event log audit USE master GO CREATE SERVER AUDIT MyEventLogAudit TO APPLICATION_LOG ALTER SERVER AUDIT MyEventLogAudit WITH (STATE-ON) -- Create a new database IF EXISTS(SELECT name FROM sys.databases WHERE name = "MyDB") DROP DATABASE MyDB GO CREATE DATABASE MYDB GO USE MyDB GO -- Monitor database for all DML actions by all users to the event log CREATE DATABASE AUDIT SPECIFICATION CaptureDbActionsToEventLog FOR SERVER AUDIT MyEventLogAudit ADD (DATABASE_OBJECT_CHANGE_GROUP), ADD (SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo BY public) WITH (STATE-ON)
The FOR SERVER AUDIT clause directs the monitored events to the server object MyEventLogAudit, established earlier to record audits in the application event log. The first ADD clause defines the DATABASE OBJECT CHANGE GROUP, monitoring database objects for DDL changes. This effectively audits CREATE, ALTER, or DROP statements targeting any database object (e.g., table, view). The second ADD clause audits DML actions (SELECT, INSERT, UPDATE, DELETE) executed by any public user on objects in the dbo schema (applying to all users).Database audit specifications offer precise control. The ON clause in the previous statement audits all objects in the dbo schema, but it can be tailored to audit DML operations on specific tables if needed. Similarly, instead of auditing all users using the public role in the BY clause, individual users and roles can be specified to audit DML actions solely by those users. To delve deeper, consider pursuing Online SQL certifications.
Ans. The Event Viewer is employed to review audits recorded in the event log (accessible through Administrative Tools in Control Panel). Demonstrates the event properties of an audit from a database audit, capturing a DELETE statement against the TestTable table using the Event Viewer.
Using Event Viewer to display a recorded audit in the Application event log.
Audits stored in the file system are not simple plain text files for viewing in Notepad; they're binary files. Viewing can be done through SQL Server Management Studio. Right-click the desired audit object in Object Explorer under the Security node at the server instance level (not the database level), and select View Audit Logs. This opens the Log File Viewer window.
Displaying file system-recorded audits using the Log File Viewer in SSMS.
Each audit entry provides detailed information about the captured event: timestamp, server instance, action, object type, success or failure, permissions, principal name and ID (user performing the action), database and schema names, object name, executed statement, and more.
Ans. The table-valued function (TVF) sys.fn_get_audit_file can be used to query audit files. This function accepts parameters specifying one or more audit files using wildcard patterns. You can also provide an initial file and a starting offset location for reading audit records. While optional, these parameters must be specified using the keyword "default." The function reads binary data from the file(s) and formats audit entries into a table.
SELECT event time, database_name, object_name, statement FROM sys.fn_get_audit_file('C:\Demo\Sql Audits\*.sqlaudit', default, default)
Querying audit files.
The query produces condensed results. This data can be filtered, sorted, or inserted elsewhere using WHERE, ORDER BY, and INSERT statements. Unlike event log audits, querying and manipulating file system-recorded audits are more versatile. For deeper insights, explore various Online SQL certifications.
Ans: SQL Server offers catalog views that provide information about running audits and audit specifications on any server instance. Lists these audit catalog views along with brief descriptions.
Audit Catalog Views
Ans: Partially Contained Databases and Their Significance Moving or restoring databases to other servers becomes problematic due to the reliance of database-specific users on server-based logins. SQL Server 2012 introduces "partially contained" databases to address these dependency issues and enhance database portability. "Partially contained" implies SQL Server allows but doesn't enforce containment. Special contained users can be created within such databases, having their passwords stored directly within the database. Unlike standard SQL Server authentication, these contained users are authenticated within the contained database. The connection string should include the Initial Catalog keyword to specify the contained database name. Online SQL certifications can provide further insights.
sys.dm_db_uncontained_entities
DMV identifies potential threats to database portability. It reports uncontained entity references within objects like stored procedures, views, functions, and triggers, assisting in identifying dependencies that might cause portability issues.Ans. Security Considerations for Connecting SQL Server to the Internet Directly exposing any OS or application to the Internet without a firewall is risky. Such exposure makes systems vulnerable to attacks. Microsoft invests in default security measures, but misconfigurations can still occur. Protect systems using firewalls and isolation techniques to mitigate risks.
Understanding and addressing SQL Server vulnerabilities and hacker tactics is paramount in maintaining the security and integrity of your database system. By following best practices, configuring your environment securely, and staying informed about potential risks, you can effectively defend your SQL Server against malicious attacks.Remember to regularly update your system, implement strong authentication and access controls, and conduct thorough security audits.
SQL Server MERGE Statement: Question and Answer
Mastering INSERT and OVER DML Syntax: Interview Questions Guide
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Download Syllabus
Get Complete Course Syllabus
Enroll For Demo Class
It will take less than a minute
Tutorials
Interviews
You must be logged in to post a comment