How to audit all SQL Queries in a database?

How can you audit all queries within a database?

Hi Joshua,

there is builtin functionality for this. Youcan either implement your own logic at your frontend for doing so, using an additional layer between the database on the data components or only use stored procedure to retrieve the data with logging the actions inside the stored procedure.

If you want to use a third party logreader like lumigent you can explore the transaction logs for statements.

-Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Joshua,

Look for SQL Trace or Profiler in Books Online.

|||Audit sounds to me (could be to my non-native language english) as you want to audit or trace SQL queries for a long time. profiler is very costy and should only be started as a debugging tool, not for running it all the time. I just wanted to make sure that you keep that in mind. Otherwise, using the Profiler for a short amount of time to find out problems and queries fired against the database is a usual and daily business for developers and administrators.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

If your interest this in the control of changes in the registries of a table, you can use triggers to before know the state the data of the registry and despues of being updated.

Respuesta en idioma original:

Si tu interes esta en el control de cambios en los registros de una tabla, puedes utilizar disparadores para conocer el estado de los datos del registro antes y despues de ser actualizado.

|||Profiler is very costly, but you can set up a job to run a trace, just for certain types of events, like Batch Complete and RPC Complete, which will capture all of the queries run. If you script this in a job, then direct the results to a trace file on the hard drive of your server, the cost is minimal. You can then use fn_get_traceinfo to review the trace files, and even load them into a database table for analysis.|||Thanks Allen, this was helpful. I'll give it a try.

How to Append Multiple Queries to a Temp Table?

Hello. I'm having some difficulty trying to output the results of two seperate queries into the same temporary table.

Does anyone know if it is possible to use the UNION operator to output the results into a temporary table?

Select * From TableA
UNION
Select * From TableB
<--output result to temporary table here-->

Alternatively, is it possible to output the results of two queries in to the same temporary table without the UNION clause?

The following statement fails on the second SELECT INTO due to the fact that #MyTempTable already exists.

Select * INTO #MyTempTable FROM TableA
Select * INTO #MyTempTable FROM TableB

Thanks in advance.I think if you specify the SELECT * INTO in the top half of your union it ought to do the trick.

Yes this works...

USE NORTHWIND
GO
SELECT * INTO EMPLOYEES2
FROM EMPLOYEES
UNION ALL
SELECT * FROM EMPLOYEES

Points deducted for style though, never use SELECT * in production. Need a column list, do this...

SELECT COLUMN_NAME + ', '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Employees'
ORDER BY ORDINAL_POSITION|||Thanks Thrasymachus - that's exactly what I was looking for.

...and I'm sure you'll be pleased to hear the SELECT * was only for the example's in this thread. :D

How to access tables from a dynamic

I have a procedure that takes database name and queries some tables in the database specified. I am trying to access tables like this:

SELECT COUNT(id) FROM @.dbname..sysobjects WHERE type='U'

How can I access the tables while the database name is in a variable.

Thanx.I have a procedure that takes database name and queries some tables in the database specified. I am trying to access tables like this:

SELECT COUNT(id) FROM @.dbname..sysobjects WHERE type='U'

How can I access the tables while the database name is in a variable.

Thanx.

You can do it with an exec call:

EXEC('SELECT COUNT(id) FROM ' + @.dbname + '..sysobjects WHERE type = ''U''')

The U value needs to be surrounded by 2 pairs of single quotes, because it is a string within a string.|||Hi, Thanx.

How to access SQL Server 2005 with Query Analyzer (SQL 2000)

I'm not able to run queries on a MS SQL Server 2005 using the Query Analyzer
from an XP Pro machine running MS SQL 2000. I'm able to connect to the SQL
2005 server and display all objects in the Object Browser but can't run a
simple query. Any ideas?
By the way, I'm connecting with a new SQL account I created in SQL 2005 with
full rights.
What error message(s) are you getting?
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Sal Young" <SalYoung@.discussions.microsoft.com> wrote in message
news:9BCEACF4-3E33-48B2-AB00-70AF2D087F5D@.microsoft.com...
> I'm not able to run queries on a MS SQL Server 2005 using the Query
> Analyzer
> from an XP Pro machine running MS SQL 2000. I'm able to connect to the
> SQL
> 2005 server and display all objects in the Object Browser but can't run a
> simple query. Any ideas?
> By the way, I'm connecting with a new SQL account I created in SQL 2005
> with
> full rights.
|||I'm running the following select statement to get a table does not exist error:
SELECT * FROM Address
"Gail Erickson [MS]" wrote:

> What error message(s) are you getting?
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "Sal Young" <SalYoung@.discussions.microsoft.com> wrote in message
> news:9BCEACF4-3E33-48B2-AB00-70AF2D087F5D@.microsoft.com...
>
>
|||Sal Young wrote:
> I'm running the following select statement to get a table does not
> exist error:
> SELECT * FROM Address
>
I believe this was already addressed in another post (unless we're
dealing with two identifical posts from two users).
SQL Server databases use the concept of schemas. That is, objects may or
may not be owned by "dbo" - the general default from prior SQL versions.
Because of this, the schema must be supplied so SQL Server knows where
to look. The Address table in the AdventureWorks database is contained
in the Person schema. So try:
Select * from Person.Address
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Query Analyzer does not fully support new SQL Server 2005 features. For
example, it does not recognize schema names in the Object Browser. So when
you look at the AdventureWorks objects in the Object Browser of QA, they all
appear to belong to dbo even though very few objects actually do. If you
want to continue to use QA for 2005 databases, you'll need to keep that in
mind. The SQL Server 2005 Books Online topic "AdventureWorks Data
Dictionary" lists all the tables and the schemas they are contained in.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%234sqZta1FHA.4032@.TK2MSFTNGP15.phx.gbl...
> Sal Young wrote:
> I believe this was already addressed in another post (unless we're dealing
> with two identifical posts from two users).
> SQL Server databases use the concept of schemas. That is, objects may or
> may not be owned by "dbo" - the general default from prior SQL versions.
> Because of this, the schema must be supplied so SQL Server knows where to
> look. The Address table in the AdventureWorks database is contained in the
> Person schema. So try:
> Select * from Person.Address
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||I may just be me but I honestly think the SQL Server Management Studio is one
of the worst tool I have ever used. I wish they still had enterprise manager
and query analyzer for 2005. The new tools are so slow an bloated.
"Gail Erickson [MS]" wrote:

> Query Analyzer does not fully support new SQL Server 2005 features. For
> example, it does not recognize schema names in the Object Browser. So when
> you look at the AdventureWorks objects in the Object Browser of QA, they all
> appear to belong to dbo even though very few objects actually do. If you
> want to continue to use QA for 2005 databases, you'll need to keep that in
> mind. The SQL Server 2005 Books Online topic "AdventureWorks Data
> Dictionary" lists all the tables and the schemas they are contained in.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:%234sqZta1FHA.4032@.TK2MSFTNGP15.phx.gbl...
>
>