Hot answers tagged

30

I would suggest you to create a server side trace or enable SQL Audit to track down activity from users that you don't trust. Remember that DMV data gets reset if the DMV is cleared out, SQL Server is restarted, etc. The closest you can get is using below query: /****************************************************** Script : Findout Who did what ? Author ...


13

If you only have around 50 tables, I personally wouldn't bother with a solution that uses a cursor. You can write a SQL query that looks at SQL Server object catalog views to generate the SQL code that you need to run to add the columns. One nice benefit is that you can easily review your code before running it. I think that the below query will be close to ...


11

Is there a way to pass information onto the Delete trigger such that it could know who deleted the record? Yes: by using a very cool (and under utilized feature) called CONTEXT_INFO. It is essentially session memory that exists in all scopes and is not bound by transactions. It can be used to pass info (any info--well, any that fits into the limited space) ...


11

When designing versioning capabilities in your data, there are several minimal (I would think) requirements: Each version of the data should be self-contained and independent of other versions. This means no flag or other indicator showing which is the current version and which are "history." It also means updating the entity means inserting a new version ...


10

Personally, I would use triggers for auditing. If they fail, then the main DML gets rolled back (same) The main update requires the DML to succeed A table will typically have several DML paths You may not be able to use code You will have direct table update that bypass code at some point Ok, so we'll use triggers. Some notes Ensure they work for multiple ...


10

As you've identified, storing the price on the order makes the technical implementation easier. There are a number of business reasons why this may be beneficial though. In addition to web transactions, many businesses support sales through other channels, e.g.: Over the phone Sales agents "on the road" At a physical location (e.g. shop, office) In these ...


7

Run this in the context of the master database as you must be running it under the context of a user database set to SQL Server 2000 compatibility level. The following should work fine USE master; SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query] FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys....


7

Most likely you did. Most cases database files, including transaction log, are configured for auto-growth. Auth-growth enables the file to increase its size as needed, any time is needed. The trigger for growth can be any activity that requires log space. The activity that triggered the growth is most cases ordinary, normal activity. The true problem is why ...


7

There's an application that will create triggers for you - ApexSQL Audit. You can test the behavior then, but based on what you said, it's not a high transaction database, so there should be no problems. One more resource you can use to audit data changes is reading transaction logs.


7

SQL Server does not keep track of who does what without you setting it up, now is a great time to set this up because you'll catch it the next time. You can try and read the Transaction Logs to see what happened (minus the principal account used to make the change), but otherwise you'll want something set up in the future. The best approach to see who ...


7

Extended Events can capture all the events that are traced by auditing? Yes and no. The built in auditing framework is built upon the extended events framework. This means any of the built in auditing will be serviced via the XE framework, assuming it is setup. Can we get all the functionalities of auditing just by using XEvents? You can get most, but ...


6

SQL Server Audit leverages the new Extended Events architecture introduced in 2008. If you look at the following DMVs, you can see the related structures appear when the audit is started. select * from sys.dm_server_audit_status select * from sys.dm_xe_session_events select * from sys.dm_xe_session_targets Note that audits have their own set of special ...


6

You could do that with DDL triggers ([before|after] grant or revoke). The docs are in the Oracle® Database PL/SQL Language Reference. You should look carefully at the event attribute functions table, and the Event Attribute Functions for Client Event Triggers section to know what information is available in those contexts. Here's an example trigger (...


6

Since you're using SQL 2012, you can leverage SQL Server Auditing, which was introduced with SQL 2008. SQL Server Auditing can be used to track a multitude of actions within SQL Server, include database object permission GRANT/REVOKE/DENY actions. You'll be able to see the security principal that executed the statement, when the statement was run, and what ...


6

If you didn't enable auditing in your database, you usually cannot spot who and when queried and/or modified the database, at least as easily as with auditing enabled. You can use various types of auditing for these tasks: Standard auditing Fine-Grained Auditing (FGA) Triggers With standard auditing, you can audit object and system privileges, such as ...


6

Sample data with the Date column typed as date: CREATE TABLE dbo.Staff ( [ID] integer NOT NULL, [Surname] varchar(5) NULL, [FirstName] varchar(4) NULL, [Office] varchar(9) NULL, [Date] date NOT NULL, PRIMARY KEY (ID, [Date]) ); INSERT INTO Staff ([ID], [Surname], [FirstName], [Office], [Date]) VALUES (001, 'Smith', 'Bill', 'Melbourne', '...


6

Since you've already added the successful login auditing to the instance, you can dump the error log data to a #temp table, then parse the individual login names, and then find all the rows in sys.server_principals that don't exist in the filtered data. CREATE TABLE #x ( LogDate datetime, ProcessInfo nvarchar(max), [Text] nvarchar(max) ); DECLARE @dt ...


6

The following process has worked well for us. We write our audit files to a file share. I have a Sql Server instance dedicated to processing these audit files. On this SQLAudit instance, I have a job that runs every minute and executes a stored procedure. The stored procedure: Uses Powershell to move any audit files (that are not currently being ...


6

There are a few options that I was able to get working. All of the options deal with variations of filter predicates. NOTE: you must disable the Server Audit in order to make changes, and then re-enable it. First, the most generic approach is to filter out all Scalar UDFs. You can do that by using the class_type audit field. The documentation indicates that ...


6

You can use Extended Events™ to accomplish this. The error_reported event allows you to log a row to a file every time a certain error occurs. You can add additional information to what is logged such as username and client_hostname. Here is some example T-SQL to get you started that filters on only error code 262: CREATE EVENT SESSION [error_262_to_file] ...


5

fn_dblog is the way to look backwards as the other commentators have said. Only allowing the users to modify the data through stored procedures is a great way to prevent this happening in the first place, as you can add logic to prevent users from mass modifying records they shouldn't, or ensuring that they have to provide correct values for updates. This ...


5

I'd probably stick with triggers, for the same reasons gbn described, but if performance becomes an issue, you may want to consider using Service Broker to perform the work asynchronously (if business requirements will allow that). The typical design would be to have a trigger call a stored procedure that sends a Service Broker message, and the Service ...


5

The closest match to what you're looking for seems to be the SQL Server Audit feature, which was added in SQL Server 2008 as a replacement/successor for SQL Trace. I recommend following the link to read up on it, since its setup/use is too big a topic to describe here in any meaningful way.


5

You can't do this at the audit specification level, but you can do it at the server audit level. So, if this audit does other things too, and you don't want to filter out those events by login, you will need to create a separate audit. Assuming you want to filter out all activity of any kind by this service account, you can do this: ALTER SERVER AUDIT ...


5

You can't that way, unless you are looking to record the SQL server user ID rather than an application level one. You can do a soft delete by having a column called DeletedBy and setting that as needed, then your update trigger can do the real delete (or archive the record, I generally avoid hard deletes where possible and legal) as well as updating your ...


5

I would probably go with triggers. You will be warned about performance implications and all that, but truthfully there isn't a "free" way to audit, and any option you try will have some impact on your workload. You will need to be aware that if someone updates the whole table, you will be logging that too, so be prepared for your auditing data growth to ...


5

I just wrote this earlier today. It's a select statement working off the information_schema database, which produces the schema for the audit tables and the triggers. SET GLOBAL group_concat_max_len = 1000; SET @dbName = "[[[your_db_name_here]]]"; SELECT concat("DROP TABLE IF EXISTS `", @dbName, "`.`", table_data.audit_table, "`;\r", "CREATE ...


5

You can login with (IP,Port Number) to remote server.By default the SQL Server don't log the logins. If you have pretty clean logs then you shall not get login history data. if you have configure SQL Login Audit before pretty clean log. Then some point of time there will be changes that to get the 'SQL Login Audit' details through TSQL like EXEC ...


5

I am not sure about CDC, but if the login has view server state permission you can use DMVs to get some information. This is given in Books Online here. I changed the query to add columns which would give you the IP address: SELECT c.session_id, c.net_transport, c.encrypt_option, c.auth_scheme, s.host_name, s.program_name, s.client_interface_name,...


Only top voted, non community-wiki answers of a minimum length are eligible