Audit All SQL Queries Against All Or Specific Databases
Sep 21, 2006
Hello,
I have a problem. I suspect a certain user in my company is using the SA account to log into Databases and run queries(query analyzer) to gather information this user is not suppose to view. The problem I have is this is an IT person so they know the SA password. How can I log specific or all queries run against all databases/tables/fields to find out exactly what this person is doing/seeing? Or if there is an alternative to find out this information?
Currently I am using SQL server 2012 and would like to implement database audit specification on specific users in my database. These are the users in my database name Payroll :-
In my database audit specification settings, I would like to capture any SELECT,UPDATE,DELETE and EXECUTE command for users PayrollAndy.Bred & PayrollArpit.Shah only since they owned db_owner access. However, I am unable to capture any single command from both users. I do not want to put 'Principal' as public since I just want to capture both users activity.
Is it I miss out anything? Is it because of windows login account?
I need a trigger to know who and when a char(1) column is changed. Â Would like to write the audit trail to its own table I can query and record before and after values.
DDL:
CREATE TABLE [dbo].[Test]( [Customer] [varchar](12) NULL, [Active] [char](1) NULL DEFAULT ('N') --Must use char 1 b/c more than 2 possible values )
Insert into Test (Customer, Active) Values ('Acme','Y')..I want trigger to tell me whowhenwhere this value was changed. Â If using sql auth capture client windows id if possible and write to audit table Update Test set Active = 'N'
We are migrating to SQL Server 2005 from 2000 and we have a view created in many of our databases. That view must be changed after the migration because it uses system tables and does not return the correct information in SQL Server 2005.
To do that, I want to create an SSIS-package that loops through all the databases on a particular server. If that view exists in the database, I want to run a script that change the view.
What I try to do:
Set up a For Each Loop container with Foreach SMO Enumerator Set the connection to my server Set the Enumerate property to "SMOEnumObj[@Name='Databases']/SMOEnumType[@Name='Names']" On the Variable Mapping page, place Index 0 in Variable User::dbName In the For Each Loop, place a script task to msgbox the value of User::dbName
This all works good. The problem comes when I try to nest the For Each Loop
Inside the "For Each Database Loop", place a new For Each Loop container with Foreach SMO Enumerator I connect the Msgbox Script Task with the new For each loop Use the same connection Set the EnumURN property in the Expression Builder to "Database[@Name='" + @[User::dbName] +"']/SMOEnumObj[@Name='Views']/SMOEnumType[@Name='Names']" On the Variable Mapping page, place index 0 in Variable User::tabName In the For Each Loop, place a script task to msgbox the value of User::tabName
When I try to run the package now, it does not at all.
In my Progress tab I see that Validation has started and completed for each task, but I see now errors. Nothing has run.
Test 1: I change the DelayValidation for the Inner Loop to True
Now the package runs, but I never get to the script task in the inner loop.
My main task is to create a login under a specific database.
For which I'm validating whether that login already exists or not I'm checking it in the syslogins table. I need to specifically check it is in my databse or not....for that what should I do...
What is the best approach to handle this situation? I have three different databases, which has it's own stored procedure. I need to call them all at page load and piece together the data. The common demoninator is the date.
2007 JAN FEB MAR APR
row 1 50 60 89 63
row 2 44 21 62 46
2006 JAN FEB MAR APR
row 1 60 90 65 41
row2 984 650 452 762 Row 1 and Row 2 come from two different databases and stored procedures. How can I query the data and present it as it's shown above? Thank you!
I have a situation where I need to do some reporting for simular asset records but the data is listed on different database from the same server. I was curious as to the way to write the queries that I will need.
Lets say I have 3 databases and I am looking for a asset of some type and the ID is a serial number. The serial will be on the main table from each database. Is there a way I can set a relation from a view between these records?
I.E.: 1: Database A -DSNA_tblMaiin - Serialnumber 2: Database B -DSNB_tblMaiin - Serialnumber 3: Database C -DSNC_tblMaiin - Serialnumber
I have 2 DBs located on separate Sql Servers but the DBs are linked. I am querying data from both DBs but want to combine the results. Here is my query but it doesn't seem to be working.
(SELECT DISTINCT idname, name, address, address2, awardedtoname, suppno FROM contract INNER JOIN house ON contract.idname = house.idname) JOIN (SELECT DISTINCT tpd.PropertyNumber AS [Property No], tpd.Address1 + ' , ' + tpd.Address2 AS Estate, tpd.Address1 AS Address1,
I have made a server security audit and specify from database audit specification to audit "select" on a certain user and on a certain table. I logged in by this user and made the select statement..when i run this query
"select * from sys.fn_get_audit_file('d:Auditaudit1*',null,null)"
It return a value at which time the query has done
after 15 minutes i repeated the same action, i run the audit query and the same result is showed off on the panel.is it suppose to return a list of values by how many times this user has made the select statement on that table ? for example at 5:00 pm then 6:00 pm and so on
I need help...here is the problem.Last weekend, the servers in our datacenter where moved around. After thismove, and maybe coincidental, 1 server is performing very poor. Afterrunning a trace with SQL Profiler, I saw the problem which was laterconfirmed with another tool for SQL server performance monitoring. It seemsthat all connections to the SQL server (between 200 - 400) are doing a login/ logout for each command that they process. For example, the user'sconnection will login, perform a SELECT, and then logout. This is not a..NET application. The client software was not changed, it is still thesame. The vendor has said that it is not supposed to do that, it issupposed to use 1 connection that log's on in the morning and logs off atthe end of the day or whenever the user exits. 1 user may have severalconnections to the database.At times, the server is processing over 250 login / logouts (avgeraged for30 second period). Has anyone seen this problem? I have the server inAUDIT FAILUREs only. The server has become very unresponsive, things thattook 3 seconds now take over 15 seconds.Any ideas???
We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.
Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?
I want to ship 500,000 aged transactions each night to an archive table and delete them from their source table in one or more logical units of work (LUW). Each row is approx 60 bytes and there is only one non clustered index on the source table presently.
I'm trying to weigh the pros and cons of 3 alternatives. One of them would basically insert the non-aged rows into tempdb, ship the aged records, truncate the table and then insert the tempdb records back into their source all in the same LUW.
For this alternative, I'd at least like to turn off logging when the records get inserted into tempdb as I dont see any value in logging that part of the activity. Is this possible?
Could you guys please help me with printing reports invoked thru command line/ URL access to print automatically to specific printers and specific trays and also is it possible to set the specific printer and tray as parameters.
I don't know whether anyone has this problem, but I am definitely not using the right keywords to search for a thread.
My situation is this...
I have a dataset that has values to fill cells to multiple tables in a report. However, I only want to select specific data from the dataset to fill textboxes and others. I cannot change the stored procedure, but the sample of the data is shown below:-
I know it looks weird, but the row really represents which "row" or textbox is it to fill with the Val. The Stat Column is just a way to make sure that I am filling the right values.
so my new report would have multiple tables to denote different categories. In my first table, I tried putting the cells as follows:- (expressions are highlighted in italics and bold)
I tried putting it into the headerrows instead of DetailRows, and it ended up printing the last value. Is there anyway to do this ? print all the values out in one table ? I tried using textboxes, but I think I got my expression wrong.
and it give me an error The value expression for the textbox €˜textbox5€™ contains an error: [BC30455] Argument not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.
Appreciate any advice or suggestion for this scenario !
I am very early on in developing a website to track issues with projects which is tied to a SQL database. I have my Projects Table, my Users Table, and am creating a third table to track issues. I'm wondering what is the best way to assign specific users to specific data/projects. The user should only be able to view & update the projects assigned to him. He should not be able to see other projects. What is the best way to assign projects/data to the users to make sure they are only viewing their data?
Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.
I want to register into a table each time a user creates, modifies or deletes any object in a database. It's not possible to add a trigger to the sysobjects table. What can I do?
I enabled the C2 Audit option for my SQL server and it is working allright. i am trying to figure out how can i configure the audit to run for specific databases/tables only. I have several databases on the server but I just want to audit one table in one database for PCI compliance rules. Any recommendations?
Good morning, Im having a little problem with this report I need to generate, so I thought I would ask for some advice. I have 2 tables. The 1st is the actual table and the 2nd is the log table (Employee; Employee_log). the '_log' table is an image of the Employee table except it has 4 extra fields (recID, last_updated_employee_id, operation and operation_date) recid being the PK of the log table. I need to generate a report that contains some thing like the following:
Table_Name
Column_Name
Old_Value
New_Value
Employee_Modifier
Operation
Operation_Date
Employee
LastName
Reid
Blevins
Jessica Bluff
UPDATE
2/2/2008
Employee
FirstName
Bison
Blison
Jessica Bluff
UPDATE
2/1/2008
Employee
EmployeeID
1234
Jessica Bluff
INSERT
2/1/2008
Employee
EmployeeID
75
Bill Thompson
DELETE
1/28/2008
To do this, I compare the Employee table to the Employee_log table. If I find changes (the two columns do not equal), I add that columnName and the column value for the regular table(new_value) and the column value for the log table(old value). If anyone has a solution or some sql to help me out, it would be greatly appreciated. (A query that will give me each columnName with the value per row would help; Somehow possibly joining my Employee table with 'INFORMATION_SCHEMA.COLUMNS' ??) Thanks!
I wish to track changes made to a table, including login who made the change, time of change, etc, without having to change the existing table structure, stored procedures, application. If anyone has any strategies (with a brief explanation) or articles they could point me to, it would be very much appreciated.
My thinking is to set up a trigger to write both old and new data to a audit table but considering different strategies may be helpful.
I have tried to make my basic audit log do more, but i haven't gotten very far;
In my basic audit log, i record this information:
table type of change field modified old value new value db user date/time
This audit records everything, which is great, but it cannot relate information when i go back to analyze the changes; for example, when a "directory" record is added, a user's information may be entered into several different tables, such as:
name (different table) addresses (different table) phone numbers (different table)
If one wanted to look up the changes to addresses of a person in the directory based on the person's name, i could not do it with my existing audit log because the addresses would be in a different table than the name table and there is no relating data in the audit log to relate the address changes to a persons name;
What might be a solution? I have tried a few approaches and am at a loss;
does anyone know how i can audit a servers login id's and tell the last time it was used. i have just gain about 8 sql servers with a bunch of users that i know are no longer around. so i am trying to trim out dead id's
I am currently developing a system which uses SQL Server 7 as its repositry. One of the systems requirements is the ability to be able to record any changes made to the data, and by whom. In other words I need to store the before and after with a userid.
Is there away to track which user had deleted and object(table), the transaction log has that information but you can't read it and the error log doesn't log such info. Any advice would be appricated, thanks.
Have a scenario where we have to audit all our databases and servers for changes in security accross the servers.
We have a central montioring server where we pull SQL metadata at regular intervals. In this instance we are looking to have before and after snapshots of the SQL system tables.
For the logins this is fine as there is a last updated field in the syslogins table. We can tell when a new user has been added , remmoved or the login has changed ie the login as been added to sysadmin fixed server role... etc Perfect !!!
What Im trying to work out now is how I can do this for object level permsisions. Have looked at sysprotects but no joy. If a user or a role has been granted select or update on a table... How can I tell based on before and after snap shots of the system tables what permissons have changed and whom have they changed for...
Hi all, I would just like to ask whats the best way to make some audit on some of the tables in a MS SQL server, what i'm planning to have is to have a table which can contain all changes/inserts/deletion of some given tables, my first idea was to have this:
AuditTable that have the following fields: AuditID, TableName, FieldName, OldValue, NewValue, UpdateBy, UpdateDate
then in all the given tables, i'll have insert, update and delete trigger, the issue comes down to the trigger, what will be the best way to have that trigger written in a way that it can be use for other tables as well? say if a table have more then 20 fields, I don't want to declare 20 var and compare them 1 by1, and if there is a diff, then i insert to the audittable, I want something that it can loop and (if possible) be able to use by other table as well, so the field name etc can get from sysobjects, but then how can you code it in a way that it can do that?
Or is there any better way to get the same result? currently i have an audit table for each table i want to audit on and its just wasting space, any help will be great. Thanks,
