Skip to main content

Auditing a Database in SQL Server 2005

Knowing the SPID of the current session we are using.

select @@SPID

To know the database name from the dbid

select DB_Name (DBID) and viceversa, select db_ID (DBname)

We can get the query being executed on any session using the below query.

DECLARE @Handle varbinary(64);
SELECT @Handle = sql_handle
FROM sys.sysprocesses
WHERE spid = 62;
SELECT text FROM ::fn_get_sql(@Handle)
Labels:

Comments

  1. UnknownOctober 16, 2014 at 5:29 PM

    Thanks for sharing this query. I found good information from http://www.lepide.com/sql-server-audit/ that enables the user to audit all SQL servers database within the entire network using a centralized platform and allows to audit specific user activities and operations as per your requirement.

    ReplyDelete

Post a Comment

Popular posts from this blog

Deferred prepare could not be completed

This is usually an error that is encountered while querying a remote database on a different server instance which says OLEDB provider "SQLNCLI" for linked server "ABCD" returned message "Deffered prepare could not be completed" Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. This is the way I could rectify, though this is not the lone mistake that gives this error every time. Just set the default to the database that is being querying to the user through which the Linked server is connecting to the database.
4 comments
Read more

Incorrect SET Options with Filtered Indexes

Rebuild Index|Create Index|DML on a Table with Filtered Index fails with the error: The following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934).  The step failed. The Operation above might succeed when its through SSMS. But, would fail when the operation is from a SQL Agent Job. This is because, SQL Agent by default, do not have the SET Options enabled (ON). They are OFF. Reason: The following are the SET Options that are needed while operation on a table with a filtered index. Required SET Options for Filtered Indexes The SET options in the Required Value column are required whenever any of the following conditions occur: Create a filtered index. INSERT, UPDATE, DELETE, or MERGE operation modifies the data in a...
Post a Comment
Read more

Key not valid for use in specified state - SSIS

You might see the following error while a package was newly imported and is set to be called from an SQL Agent through a job. The job would fail reporting the below error. Source:        Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error This is mainly because, the package protection level, which is by default set to  "EncryptWithSensitiveKey" Resolution: Set the package protection level to "DontSaveSensitive". But, if in case you know the password for the key, you could use it. How to change the SSIS Package Protection: In Business Intelligence Development Studio, open the Integration Services project that contains the package. Open the package in the SSIS designer. If the Properties window d...
Post a Comment
Read more