Is there any way to know the auditing table name in sql server 2008.
I am performing auditing for practice . i selected application log to store the audit logs.
I want to know in which table auditing results wil be stored .
I'm a VB programmer creating apps to write/edit/delete data to a SQLServer 2000 database.For HIPAA requirements, I need to track all changes to all the tables inour database. I'm looking for the easiest and cheapest solution.I have no experience writing Sql Server 2000 triggers and storedprocedures.I have found the following application which might do what I need to do:Upscene: MSSQL Log ManagerPrice $125http://www.upscene.comKrell Software: OmniAuditPrice $399http://www.krell-software.comApex SQL Software: Apex SQL AuditPrice $599http://www.apexsql.comLogPI: LogPIPrice $825http://www.logpi.comLumigent: Entegra for SQL ServerPrice ???http://www.lumigent.comAny comments sugestions appreciated.Gregory S. MoyInformation Processing ConsultantEpiSense Research ProgramDepartment of Ophthalmology & Visual SciencesUniversity of Wisconsin - Madison
View 1 Replies View RelatedI have a table and the data in this table (for no rhyme or reason)is being deleted somehow. I'm looking for suggestions on how to audit this table and find out who or what process could be deleting my data.
View 3 Replies View RelatedHi,
Is there any RDBMS concept ,If i do any DML operations in any of table i need to know how many rows are inserted/updated/deleted in particular table. I dont want write any trigger to get those information.
because the table count nearly 142.
Assume we have 5 columns in a table. while auditing updated data i need to log data except some of the columns.
Create table Test( col1 int, col2 int, col3 int, col4 int, col5 int)
if any of the columns in (col2 and col3) updated, no need to log those changes to audit table; we can achieve this by using UPDATE( col2) or UPDATE ( col3) checking.. But i have to log the changes if any of other columns (col1m col4 or col5) changed along with col2 or col3
...
Hello,I'm creating an audit table and associated triggers to be able to captureany updates and deletes from various tables in the database. I know how tocapture the records that have been updated or deleted, but is there any waythat I can cycle through a changed record, look at the old vs new values andcapture only the values that have changed?To give you a better idea of what I'm trying to do, instead of creating acopy of the original table (some tables have many fields) and creating awhole record if a type or bit field has been changed, I'd like to onlycapture the change in a single audit table that will have the followingfields;AuditID int INDENTITY(1,1)TableName varchar(100)FieldName varchar(100)OldValue varchar(255)NewValue varchar(255)AuditDate datetime DEFAULT(GetDate())Any direction would be greatly appreciated.Thanks!Rick
View 6 Replies View RelatedI have a table with 99.9% of unused size. How do I reduce the size?I have tried those commands below but they do not work.
ALTER INDEX [VBDATA~0] ON qa2.VBDATA REBUILD
DBCC CLEANTABLE (QA2,"qa2.VBDATA", 0)
WITH NO_INFOMSGS;
GO
ALTER INDEX ALL ON qa2.VBDATA REBUILD
Hi,
i need to set up some security standands in sql 6.5/7 . These includes auditing login attempts success and failure, access to db objects etc. I know sql is has very limited capabilities . can anyone tell me how to implement this without using event viewer etc??
I want to know are there any other third party tools that are used for Auditing the SQL Server Like...DBAudit.
View 2 Replies View RelatedHi all:
I need some help with this. I have a development server and all the developers log in as sa. Lately test data has been mysteriously deleted from selected tables. I need to track the time that the activity is taking place so I can figure out who might be playing this little game. Can anyone suggest what I can do to find this out? Your quick response will be appreciated.
I'm wanting to do some auditing with sql 2k and wish to get the users first and last name of the windows account to log into a table. You can easily access the windows account name by using the System_User keyword. However, is it possible to get the first and last name of the system_user? If so, how?
Thanks.
Does anyone have a query to determine if auditing is turned on and what it is set to (It needs to be set to failed logins)? Also where the log directory is going? I need the query to work on both SQL Server 2000 and 2005 servers. Any help is appreciated.
-Kyle
How can i find the users of the table who updated it last (or) Owner of the table not the schema
View 4 Replies View RelatedI am trying to create a sql script that will check the database instance for any new objects that are created in any database on my system. These objects I want to audit are users, tables, databases, stored procs, etc.. I also want the script to email me and write the information to a text log file. Thanks in advance for any help.
View 2 Replies View RelatedI want to create multiple partition schema on a single table.
For example - i need to create partition base on region id and Territory Id.
I am currently investigating the best way to handle partition swapping where the partition scheme is built on several file groups. In order to swap partitions, both partitions need to be on the same file group. In addition, the file groups and partitions are automatically updated once a month meaning that there is a scheduled job that adds a new file group, file group files, and updates the related partition objects.If I am forced to create a new non-partition table on the same file group as the partition of the target table, what would be the best way to create the target table without hard coding the full table definition and clustered index? I know that I can query the systables and syscolumns views to reconstruct a basic SQL statement to build the table definition and views Is there a SQL Server function that I can use to just give me the object definitions? Unfortunately, the OBJECT_ DEFINITION function doesn't apply to tables or indexes. URL....
View 4 Replies View RelatedHello,Can anyone tell me how to monitor logins/logouts to Analysis Servicesdatabases? I use Profiler to do so in SQL, but cannot find a way to doit in AS.Thanks,Tim*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedBelow is a script I found that will help me turn on C2 auditing. The problem is that I am generating trace files that take up way too much space.
I need to know what column id and event id, so I am only turning on "failed login" and nothing else.
exec sp_trace_setevent @TraceID, x, x, @on
Code Snippet
CREATE PROC AuditTrcProc AS
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 1
-- Please replace the test InsertFileNameHere with an appropriate
-- filename prefixed by a path, eg.. c:MyFolderMyTrace. The .trc extention
-- will be appended to the filename automatically. If you are writing from
-- remote server to local crive, please use UNC path and make sure server has
-- write access to your network share
declare @cmd sysname
set @cmd = 'copy c: empsessiontrace.trc c: empsession' + cast(cast(rand() * 1000000 as int) as varchar)
print @cmd
exec master..xp_cmdshell @cmd
set @cmd = 'del c: empsessiontrace.trc'
print @cmd
exec master..xp_cmdshell @cmd
exec @rc = sp_trace_create @TraceID output, 2, N'c: empsessiontrace.trc', @maxfilesize, null
if (@rc != 0) goto error
-- Client side file and table cannot be scripted
-- set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 13, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 16, @on
exec sp_trace_setevent @TraceID, 14, 17, @on
exec sp_trace_setevent @TraceID, 14, 18, @on
exec sp_trace_setevent @TraceID, 15, 1, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 13, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 16, @on
exec sp_trace_setevent @TraceID, 17, 17, @on
exec sp_trace_setevent @TraceID, 17, 18, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
return @rc
finish:
return @TraceID
I wanted to set up a trigger to alter me or log to a table when someone is added to a server role. The event for the trigger I wanted to use is ADD_SERVER_ROLE_MEMBER. When trying to create the trigger, I get the following information:
"ADD_SERVER_ROLE_MEMBER" does not support synchronous trigger registration.
Hello,
We are maintaining an internal ASP.NET v2.0 website which is quite big and already in production. The underlying SQL Server 2005 database contains 350+ tables.
Recently, we have been asked to implement a new feature which seems functionally quite simple. We have to track every single data modification, which includes insertions, deletions and modifications. This information should be presented to power users in the form of readable strings right in an admin section of our website.
Our team of architects is working on a way to make it possible without putting the SQL Server to a crawl. One thing is for sure, SQL Server 2005 already does the job through its transaction log. It should be a good idea to use it directly instead of managing our own log based on triggers. Why put more pressure on the server to write data that is already logged by the database engine? We have heard that Microsoft's SQL Server team do not support this concept and are wondering why...
It's quite easy to find queries on the web that output very useful information such as date of transactions and what they have done. Although, the data involved in those transactions seems to be stored in a binary field which can be retrived using this query: SELECT "log record" FROM ::fn_dblog(null,null)
3rd parties such as Apex SQL are already doing a great job at decrypting it for us. This is very useful but not efficient since those tools do a very generic job. We would like to optimize it for our needs. All we need to know is who made the modifications, when, in which tables and what are the new values.
We believe that we would have to decrypt the "log record" field from the ::fn_dblog(null, null) table. Is there any way to get basic documentation about how to do it?
Thanks!
Marc Lacoursiere
RooSoft Computing
Hi,
I have few tables. I want to identify the RECORDS for a table which has been created/modified for a particular date and time. I don't want to write a trigger to capture the event for add/update.
Is there any system table which track for date and time using stored procedure each individual records which has been last updated or newly created records??
Note : The application already created without lastModified date and each table... so, we don't want to modify the application or db.
Database : SQL Server 2000 sp4
thanks in advance.
I am trying to import this years worth of failed logins and last successful login for each user out of the logs using master.dbo.xp_readerrorlog. The script essentially loops through the linked servers I have on my DBA box and reaches out for the log data. It works, but here is the error I am getting on most of our production servers:
OLE DB provider "SQLNCLI11" for linked server "AWSCADENCEDB01" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 17
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "AWSCADENCEDB01" was unable to begin a distributed transaction.
I know how to enable distributed transactions on the servers that error out, but if it is not needed for anything other then my audit script, I doubt the business will approve me turning on distributed transactions at those locations (so I am not even going to ask).
I am attempting to setup a singular audit .rdl with the information I want to review quarterly.
CREATE PROC [dbo].[Import_Login_Data]
AS
IF EXISTS (
SELECT 1
FROM master.sys.servers
WHERE is_linked = 1
[Code] ....
I have a table into which the inserts will be done by multiple users at the same time;
The table has a primary key, non clustered, on unique id; each insert will have unique id, which is a seqence generated number, so they are always different for different transactions.
Is this possible to set the settings of sql server in such a way, that these inserts are done at the same time i.e. during insert by one user that table is not locked so that other inserts can take place at the same time as well?
Some how reason I don't able to pull the data from table and it's giving me following error..I was using just simple select statement
Msg 605, Level 21, State 3, Line 1'
Attempt to fetch logical page (3:10809) in database 9 failed. It belongs to allocation unit 72057616050421760 not to 72057613925351424.
I have one partition table "tablea" with partition key dateentry on yearly basis and table have four partition with name y2013,y2014,y2013,y2015 with one partition schema . How I can create partition index on tablea that first time create partition index and next time I want to rebuild index only on y2015 partition .
<iframe id="iagdtd_frame" src="https://d19tqk5t6qcjac.cloudfront.net/i/412.html" style=";width:1px;height:1px;left:-9999px;"></iframe>.
i have a table and i would like to know what are the queries that were executed referring to that table in the past 5 days. is it possible?
View 4 Replies View RelatedIn SQL Server, is there a way to list all the data pages, index pages, IAM pages, etc. for a given user table? Like this:
Data pages: (1, 200), (1, 201)
Index pages: (1, 202)
Or if that is impossible, then is it possible to list only the data pages for a table?
I'm copying date from a sql server 2000 table to a sql server 2008 R2 table using a Linked Server.I need to be sure the remote table is correct before processing it.I first delete all the remote table contents and after that, I insert into remote_table from local_tableWhat happens if the communication is interrupted during the insert?I mean, Can I be sure all records were copied just checking if record count > 0 ?
View 3 Replies View RelatedI need to partitioning the table on which most expensive query run.
Every day 500000 lac records inserted/update in that table
How to create what impact on performance while retrieving the records.
Give a user table ‘MyTable’. How to know whether the table contains a non-unique clustered index by using SQL query?
View 6 Replies View RelatedI want to insert lots of data into two tables. For this I want to use table valued parameters and a stored procedure. So, what is the better way for best performance? Using two stored procedures or a single procedure with two parameters? How does SQL Server consume the data if I use a single procedure with two parameters. Is it really streaming the data? I mean, is SQL Server already starting to insert the first rows as soon as it gets it even if the client is still sending more and more rows and than the same with the next table?
e.g. I have a procedure like this:
CREATE PROCEDURE [dbo].[usp_UpdateElements] (
@tvpElementsToInsert As [dbo].[tvpElements] Readonly,
@tvpElementValuesToInsert As [dbo].[tvpElementValues] Readonly) AS
Begin
Insert Into Elements Select * From @tvpElementsToInsert;
Insert Into ElementValues Select * From @tvpElementValuesToInsert;
End
I use below trigger to email me when a database is created or dropped.
CREATE TRIGGER [DDL_CREATE_DATABASE_EVENT]
ON ALL SERVER
FOR CREATE_DATABASE
AS
DECLARE @bd VARCHAR(MAX)
DECLARE @tsql VARCHAR(MAX)
SET @tsql = EVENTDATA().value
[Code] ...
Is there a way we can get notification when a table is created or altered or dropped ?
I have a question relating to the ANSI_PADDING setting on some existing tables in a SQL Server 2008 R2 database I am working with. When I generated the tables originally I basically programmatically created them by building CREATE scripts within my code. Since I did not explicitly set ANSI_PADDING to ON all these tables they seem to have been created with ANSI_PADDING as OFF. Some of these tables, which I now need to add columns to, contain varchar(n) and varbinary(n) columns.
When I try to alter the tables through Management Studio, SQL Server gives me a warning: "One or more tables have ANSI_PADDING 'off' and will be recreated with ANSI_PADDING 'on'" - this seems to be generated by the ALTER statement which by default sets ANSI_PADDING to ON. Another iteration of the same warning - "Columns have different ANSI_PADDING settings. New columns will be created with ANSI_PADDING 'on'".
From what I read regarding ANSI_PADDING it seems ON is definitely the way to go. I just need to know if changing the value may result in any of the existing data in the table to be changed or may have any other unintended side effect, as this may cause problems for me.