Hi,
I need to audit any access to table (example table creditcards)
profiler work fine if table creditcards referenced
by select/ upadate/delete or procedure
but if I create view
create v_alex
as
select * from creditcards
profiler with all events would show only v_alex
Application modifing and number of views growing ...
Any way to trace accees to creditcards if it referenced by view ?
We have several applications that work with product catalog data. Data is entered and maintained, searched, and reported on. We're using CSLA business objects to create our Biz Objects and our front end apps are ASP.NET pages and web services. SQL 2k5 is our database. Currently all data is done in Factory methods in our business objects using SQL Stored Procedures and UDF's.
We want to start storing auditing and statistics data on our product searches. In SQL 2k we were using SQL Profiler to capture data and storing the information in tables, but it really wasn't very flexible and was difficult to maintain. What we want to do is every time someone submits a search we store the critiera and the results. Every time someone edits a product we want to save the old record. This will allow us to provide historical reporting and statistical reporting to our users.
In our old system the search results table was at about 3 million records. And since we've moved to a web based application we're hoping to save this information asynchronously so our search results or postbacks are not held up by saving this audit data. We were talking about writing logic into our biz objects code but it all seemed a bit slow and difficult to do asynchronously. Then I read a couple posts suggesting Service Broker.
Now we're considering either writing triggers on our tables or adding code to our factory stored procedures to send messages to Service Broker that would save the data into our audit tables but not hold up our business processes. We would be saving to the same database on the same server, but different tables.
Does Service Broker seem like it could be the right tool for this job? There looks like a bit of a learning curve and before I jump in i'm looking for some advice or direction.
Thanks, larry
Hi All,
i have a table in MS Access with CandidateId and Image column. Image column is in OLE object format. i need to move this to SQL server 2005 with CandidateId column with integer and candidate Image column to Image datatype.
its very udgent, i need any tool to move this to SQL server 2005 or i need a code to move this table from MS Access to SQL server 2005 in C#.
please do the needfull ASAP. waiting for your reply
with regards
I would like to remove an index from my database, but I need to verifythat it is not being accessed before I do that.I tried running Profiler, but it doesn't seem to be giving me theinformation I need. I put in a filter for ObjectName, but it seems tobe ignoring it - i get several entries in the profile trace, but novalue in the ObjectName column.Is there an easy way to monitor for object access?Thanks so much!SQL Servef 2000 SP4(I also have 2005, SP2, if there's a better way to do this with 2005)
View 3 Replies View RelatedHey there.
I must write a stored procedure to update a table and want to make sure the ID used in both Acceptance and Production will have the necessary access. The ID will inherit it's access to the resource though a ROLE.
Wondering if there are queries out there that can traverse for example, the roles/id's that have access to it... Thx!
After Upsizing a table to sql I linked to that table using access db
Now when i use one of my forms i get a [Invalid Object name "tablename''], not sure why but i am clearly link and the table is in sql!
Can you help!
Hi, I'm using Reporting Services on my website and for some reports, i get the timeout error after 30 secs.
I declared my object using the Reporting tools and I am using ObjectDataSource.
My report is declared in my aspx page:
<rsweb:reportviewer id="ReportViewer1" runat="server" font-names="Verdana" font-size="8pt" Width="100%" Height="600px">
My ObjectDataSource is declared in my aspx page under my report tag:
<asp:ObjectDataSource ID="ObjectDataSource" runat="server" OldValuesParameterFormatString="original_{0}" SelectMethod="GetData" TypeName="ReportsTableAdapters.ViewingTotalsTableAdapter">
My TableAdapter is declare in my Reports.xsd file:
<TableAdapter BaseClass="System.ComponentModel.Component" DataAccessorModifier="AutoLayout, AnsiClass, Class, Public" DataAccessorName="ViewingTotalsTableAdapter" GeneratorDataComponentClassName="ViewingTotalsTableAdapter" Name="ViewingTotals" UserDataComponentName="ViewingTotalsTableAdapter">
I am trying to access the TableAdapter object from my WebPage Class on the Page_Load event , so I can modify the Command.CommandTimeout. Unfortunately, I cannot find a way to have access to the TableAdater object.
Anyone has any suggestion ?
Thanks,
Richard
Hi everyone. I am having difficulties with a cursor that I am trying to write. The purpose of the cursor is to loop through all tables in a selected Database and revoke "Select" access to that table for the indicated role (RoleToRevoke). I am getting the error on the @name variable within the REVOKE statement. I have placed a comment indicating where I am getting the error. Is there a way to have sql server interpret this @name variable within the REVOKE statement? Thanks for your help.
Code Snippet
USE [Database_Name];
Declare cursorExample Cursor for
Select TABLE_NAME
from information_schema.tables
Where TABLE_TYPE='Base Table'
and TABLE_SCHEMA='dbo'
Declare @name as varchar(255)
Declare @ErrorSave as int
Declare @ErrorCount as int
Open cursorExample
Fetch Next from cursorExample into @name
SET @ErrorSave = 0
SET @ErrorCount = 0
Begin Tran
While @@Fetch_Status=0
Begin
--Getting Error on Line below on @name
REVOKE SELECT ON OBJECT::@name FROM RoletoRevoke;
IF (@@ERROR <> 0)
BEGIN
Print 'Error Revoking Access to Table: ' + CAST(@name AS varchar(75))
SELECT @ErrorCount = @ErrorCount + 1
END
IF (@@ERROR = 0)
BEGIN
Print 'Successful Revoking Select Rights on Table: ' + CAST(@name AS varchar(75))
SELECT @ErrorSave = @ErrorSave + 1
END
Fetch Next from cursorExample into @name
End
IF @ErrorCount = 0
BEGIN
COMMIT TRAN
PRINT 'COMMITTED TRANSACTION'
PRINT 'Total Tables Affected:' + CAST(@ErrorSave AS varchar(75))
END
ELSE
BEGIN
ROLLBACK TRAN
PRINT 'ROLLED BACK TRANSACTION'
END
Close cursorExample
Deallocate cursorExample
GO
Aaaaaarrgghh ! (that's better)I am trying to convert a field within my Oracle 9i Database that is oftype BLOB (but this BLOB may contain a combination of clobs/varchars orimages such as gif images, jpg images) to Microsoft SQL Server 2000using Microsoft DTS.On trying to perform this simple conversion I recieved the error "Needto run the object to perform the operation - Exception AccessViolation" from Microsoft DTS and my table that contains this BLOBfield is not converted across.After further investigation I implemented the fixes suggested by theMicrosoft Knowledge Base and "sqldts.com" but still no joy the errorkept occuring.I discovered my modifying the step in the DTS package that handled thistable conversion that contained the BLOB column that when I changed thedata type on my SQL Server target table to VARBINARY and modified thequery so that only the BLOBs that contained clobs/varchars were broughtacross that the error went away.I then proceeded to create another DTS package step that had a querythat only brought across the BLOB column that contained images such asgif images/jpeg images etc. and the error went away and the target typefor the SQL Server target table was set to IMAGE.As the data for this BLOB contains a combination of VARCHARS/CLOBS(concatanted) and also GIF IMAGES/JPEGS in the same source column withOracle 9i I require the same in my target table within SQL server asone column (and I should be able to do that with type IMAGE especiallyas it can store larger objects than VARBINARY but any source BLOBS thatcontain VARCHAR/CLOBs don't seem to want to be loaded as IMAGE theywill only load in to VARBINARY).However judging by my experiences above this doesn't seem to bepossible ?Can anyone help me out with this ?I am on Microsoft SQL Server 2000 Service Pack 4 with latest MDAC(2.8.1).Cheers,Gary
View 1 Replies View Related
Trying to do a update/insert from SQL 2005 query to Access 2003 linked table.
In the Script Transformation I get this error.
Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.OleDb.OleDbConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.
Destinatoin Oledb connection is Native OLEDB Jet 4 to Access 2003 database.
Private sqlConn As OleDb.OleDbConnection
Private sqlCmd As OleDb.OleDbCommand
Private sqlParam As OleDb.OleDbParameter
Private connstring As String
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
connMgr = Me.Connections.ConnectionOLE
'sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)
connstring = connMgr.ConnectionString
sqlConn = CType(connMgr.AcquireConnection(Nothing), OleDb.OleDbConnection)
End Sub
Any help would be appreciated.
Hello,I am getting a SqlException with title "SqlException was unhandled by user code" and then it says "Invalid object name 'Access Table'.here is my code (this is from my login page:)<script runat="server">
Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs)
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim cmdString As String = "SELECT [Password] FROM [AccessTable] WHERE" & _
" (([Username] = @Username) AND ([Password] = @Password))"
conn = New SqlConnection("Data Source=GDB03SQL;Initial Catalog=GDBRemitance;Persist Security Info=True;User ID=remitance;Password=remitance")
cmd = New SqlCommand(cmdString, conn)
cmd.Parameters.Add("@Username", SqlDbType.VarChar, 50)
cmd.Parameters("@Username").Value = Me.Login1.UserName
cmd.Parameters.Add("@Password", SqlDbType.VarChar, 50)
cmd.Parameters("@Password").Value = Me.Login1.Password
conn.Open()
Dim myReader As SqlDataReader
myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
If myReader.Read() Then
FormsAuthentication.RedirectFromLoginPage(Me.Login1.UserName, False)
Else
Response.Write("Invalid credentials")
End If
myReader.Close()
End Sub
</script> The error is comming from the myReader = cmd.Execute(CommandBehavior.CloseConnection)Thanks for any suggestions or ideas.
In my ssis 2012 package, I have a 'object' type variable with some table like records. I want to do some SQL operations like insert/update on the records in another table based on this 'Object' type variable records. Basically I want to use a MERGE statement with another physical table with the records in the 'Object' type variable.how to map/use the Object type variable in Execute sql task.I am not good in script task. How to utilize this Object variable in a Execute sql task?
View 9 Replies View RelatedI'm using a legacy application built using VB5 and SQL Server 7. After recompiling it, and putting the database in SQL Server 2012. I want to access the current login user using the SQL function SUSER_SNAME().
This is the code.
Set rdoRes = goDatabase.Connection.OpenResultset("select suser_sname()")
But I'm unable to get the current user login in the application. If I write any other SQL statement instead of this, then it runs. But only this statement is not running. Is there any security reasons for this?
It seems to me there are two common strategies for doing DB audit trails via a trigger:
1. On an update to a row, duplicate that row in another table with
identical rows, except for perhaps the extra columns which represent
change date and changed by. Eg. When there is an update to the Customer
table, record the changes in Customer_Audit.
2. On an update to a row, check which fields were updated. Then in a
common audit table record the table, row ID, field, previous value and
new value of the field.
I'm wondering about the pros and cons of each. More specifically, do
the pros and cons change if you are using an O/R mapper (I'm using
NHibernate.)
Some thoughts on method 1 . It seems nice for an O/R mapper, since you
could have, say, a CustomerAudit Object inherit from your Customer
object and just add the properties change date and changed by. A
problem with this is you're going to add a whole lot of objects - one
for each object which you want to audit. Another drawback is that it
could be difficult to generate a history for a particular property
which was updated. Let's say I want to see the history of changes to
the customer's status. I have to load a collection of CustomerAudit
objects (which could be costly). Then I have to iterate through them
and compare the status properties to generate a history of statuses.
This is a pretty labor instensive method if you compare it to method 2,
where the change is recorded by field, not row.
Some thoughts on method 2. It's nice since the changes are by field,
not row, which (as above) makes generating a history easy. On the other
hand, you can never have a snapshot of a particular object at a
particular point in time. Moreover, I'm not sure how foreign keys would
be handled elegantly. I record that customer.statusID changes from 3 to
6. I'd have to do a seperate join to the customerstatus table to get
meaning for 3 and 6 (which method 1 would do automatically).
Thoughts? Any preferred way to do this with an O/R mapper?
Thanks
Hello,
I'm working on Sybase and recently started working on MS SQL Server. Can anyone guide me how to set up auditing for errors whether fatal or not?
Any help is appreciated
Thanks
Dinesh
A few days ago I saw a article on the internet (i don't know where), which described a way for auditing all database activities (like deleting records per user etc.) Unfortunally I can't find this document. Can anyone help me with this?
View 1 Replies View Relatedhow to audit DTS ?
80 % of DTS run from
exec master..xp_cmdshell 'dtsrun ....
20 % started by server agent
I have list with 200 DTS
I want to delete dts that are not in use.
Can I use profiler or it its possible to get info from system tables?
Thank you
Hello,
I'm working on Sybase and recently started working on MS SQL Server. Can anyone guide me how to set up auditing for errors whether fatal or not?
Any help is appreciated
Thanks
Dinesh
Guys,
We looking to audit tables in the database. In the tables auditing needs to be done only on few columns not all the columns. Is there any 3rd party tool available to do it can it auditing be done on columns only with sql server 2005. Trigger doesnt work in this scenario as it works per row it does not say which column in the row was updated/inserted/deleted.
For example in the below employee table, I want to audit(history) only ssn and dob columns - all the updates/inserts/deletes made to this column should be audited.
empidempname ssndob
1John,smith 122222222 07/29/2001
Any suggestions and inputs would help
Thanks
I 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,
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??
Hi folks. I am a new DBA and working on taking over several existing MSSQL 6.5 applications.
My company needs to be able to audit the following actions on MSSQL.
login
logout
insert into specific tables
update in specific tables
delete from specific tables
I am looking for a 3rd party product that provides these services for SQL 6.5. I have found Braintree products and they have really nice auditing but for Oracle and not MSSQL. I have also looking into using triggers to build an audit log for me but the applications we are using require periodic updates that would force me to rebuild the triggers. This may cause too much administrativia.
Any Ideas?
Thanks, Aaron
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.
Someone had changed the SA password on one of my servers. I need to find out who did this. Can you tell me if there is any historical information kept on any of the system tables that can tell me who (what machine name) and when (date and time)this was done?
Does anyone have a 3rd party or inhouse developed task/procedure to report this kind of security issues?
hey all, i found this auditing trigger, currently it just kicks out what was changed and when, id like to add who cause the trigger to fire as well (currently its just set to the "inventory" table). check under the "add the audit fields" comment for info:
CREATE TRIGGER TRG_inventory
ON [DBO].[inventory]
FOR DELETE,INSERT,UPDATE
AS
DECLARE @ACT CHAR(6)
DECLARE @DEL BIT
DECLARE @INS BIT
DECLARE @SQLSTRING VARCHAR(2000)
SET @DEL = 0
SET @INS = 0
IF EXISTS (SELECT TOP 1 1 FROM DELETED) SET @DEL=1
IF EXISTS (SELECT TOP 1 1 FROM INSERTED) SET @INS = 1
IF @INS = 1 AND @DEL = 1 SET @ACT = 'UPDATE'
IF @INS = 1 AND @DEL = 0 SET @ACT = 'INSERT'
IF @DEL = 1 AND @INS = 0 SET @ACT = 'DELETE'
IF @INS = 0 AND @DEL = 0 RETURN
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[AUDIT_inventory]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
BEGIN
-- CREATE A MEMORY TABLE CONTAINING THE FIELDS AND TYPES OF THE TABLE
DECLARE @MEMTABLE TABLE
(
ID INT IDENTITY
,COLUMNAME SYSNAME
,TYPENAME VARCHAR(20)
)
-- INSERT THE COLUMNAMES AND THE DATATYPES
INSERT @MEMTABLE
(COLUMNAME,TYPENAME)
SELECT NAME,TYPE_NAME(XTYPE)
FROM SYSCOLUMNS
WHERE ID = OBJECT_ID('[DBO].[inventory]')
ORDER BY COLID
DECLARE @CUR INTEGER
DECLARE @MAX INTEGER
DECLARE @SQLSTR AS VARCHAR(8000)
DECLARE @CURCOL SYSNAME
DECLARE @COLTYPE AS VARCHAR(10)
-- SETUP VARIABLES
SET @SQLSTR = ''
SET @CUR=1
SELECT @MAX = MAX(ID) FROM @MEMTABLE
-- LOOP EVEY FIELD
WHILE @CUR <= @MAX
BEGIN
-- GET VALUES FROM THE MEMTABLE
SELECT @CURCOL = COLUMNAME,@COLTYPE = TYPENAME FROM @MEMTABLE WHERE ID = @CUR
IF @COLTYPE = 'INT' OR @COLTYPE = 'BIGINT' OR @COLTYPE='UNIQUEIDENTIFIER'
-- WE DO WANT TO COPY INT/BIGINT/UNIQUEIDENTIFIER FIELDS BUT IF THEY ARE AN
-- IDENTITY OR A ROWGUIDCOLUMN WE DO NOT WANT TO COPY THOSE ATTRIBUTES
SET @SQLSTR = @SQLSTR + ' CAST('+@CURCOL + ' AS '+@COLTYPE+') AS [' + @CURCOL +'] '
ELSE
-- ANOTHER FIELD DO NOTHING JUST COPY IT AS IT IS
SET @SQLSTR = @SQLSTR + ' '+@CURCOL + ' AS [' + @CURCOL +'] '
IF @CUR <= @MAX - 1 SET @SQLSTR=@SQLSTR + ','
SET @CUR = @CUR + 1
END
-- ADD THE AUDIT FIELDS
SET @SQLSTR = @SQLSTR +',CAST('' '' AS CHAR(6)) AS TRG_ACTION,CAST(GETDATE() AS DATETIME) AS TRG_DATE ' - tryin to add who made the update here, figure out what GETUSER translates to
-- SET UP THE SELECT FOR CREATING THE AUDIT TABLE
SET @SQLSTR = 'SELECT TOP 0 ' + @SQLSTR + ' INTO [DBO].[AUDIT_inventory] FROM [DBO].[inventory]'
EXEC(@SQLSTR)
END
IF @ACT = 'INSERT' INSERT [DBO].[AUDIT_inventory] SELECT *,'INSERT' ,GETDATE() FROM INSERTED
IF @ACT = 'DELETE' INSERT [DBO].[AUDIT_inventory] SELECT *,'DELETE' ,GETDATE() FROM DELETED
IF @ACT = 'UPDATE' INSERT [DBO].[AUDIT_inventory] SELECT *,'UPDATE' ,GETDATE() FROM INSERTED
Is there a tool that can report transaction log information? Such asreporting what tables/columns were updated, by whom and when,etc....Thanx.
View 1 Replies View Related
Hello
I am more of a reporting person, recently I was asked to create stored procedure for an upcoming ASP.NET
application.
We have a problem that we are facing and any suggestion would be very helpfull.
The problem is that we have like 8 differtent tables each with 10 to 15 columns in it.
The front end application has pages with save , update , delete button which are insert,update,delete
for each of the 8 tables
i.e they save , update , delete these 8 tables
They want to a way to Update , this audit table which stores information
like ---
Date
User
Table
Column name
Previous column name
new column name
So for each row that was updated in those 8 tables , each column will have the above fields updated as a row of data
Initially we thought about triggers but it will be like 60 triggers ...Is there a better or other way of handling this?
Any help is appreciated
Thanks
I want to log all changes made to a table (only updates, since there will be no deletes or inserts).
I would like to see the user who changed it, the date and time, fieldname, old value, new value.
If more fields are changed during the update, than add more records into the logging table.
What is best practise to achieve this?
Thanks,
Frans
Hi
I would like to create a simple trigger on a Customer table to fullfill two fields, on should be filled when a new customer is inserted (DateAdded) and the other when the customer is edited (DateEdited), Yes I know it is quite simple but as I am not a database expert I do not know how to solve this problem.
Thank you in advance.
Hi everyone,
I am to try to find documentation about C2 auditing into SQL Server 2005. The only one doc I fand about it is:
SQL 2000 C2 Admin and User Guide
It cover SQL Server 2000 and Windows NT4.0.
I do not find any document who cover the security from SQL Server 2005.
Can you help me please?
Thx.
Laurent
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.
Greetings SSIS friends,
Do any of you guys have a link to a website with an example of how to create a solid audit system for SSIS packages?
Thanks for your suggestions in advance.