locked
A view named 'types'. RRS feed

  • Question

  • Hi everyone!

      After created server audit and database audit specification to trace SELECT, INSERT, UPDATE, DELETE, EXECUTE on a user database, it audited the following:

    --

    class_type_desc                     object_name                              operation_desc
    ----------------------------------- ---------------------------------------- ------------------------------
    table                               tblogmidiag100                           insert
    table                               tblogmidiag100                           select
    table                               tblogmidiag100                           update
    table                               veiculos                                 select
    view                                types                                    select

    (5 row(s) affected)

      Perhaps, I can't find no view named 'types' on the user database:

    --

    GRANT select ON dbo.types TO rftplink;

    GO

    Msg 15151, Level 16, State 1, Line 1 Cannot find the object 'types', because it does not exist or you do not have permission.

      Where did it come from? May someone help me?

      I am running... 

    Microsoft SQL Server 2012 (SP3-CU6-GDR) (KB3194724) - 11.0.6567.0 (X64) 
    Oct 10 2016 18:45:52 
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: )


    Thanks.


    Doria

    Wednesday, August 15, 2018 1:17 PM

Answers

  • that is sys.types view its referring to I guess

    https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-types-transact-sql?view=sql-server-2017

    It contains details on system and user defined data types


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by dydoria Wednesday, August 15, 2018 5:10 PM
    Wednesday, August 15, 2018 1:20 PM
  • Fn_get_audot_file also returns a column named schema_name. So just add that to the columns you return:

    ...

    LEFT(LOWER(AUDI.object_name), 40) AS object_name,

    LEFT(LOWER(AUDI.schema_name), 40) AS schema_name,

    ...

    Btw, I wouldn't like those LOWER in my report, but I assume you have your reasons... :-)


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by dydoria Wednesday, August 15, 2018 5:10 PM
    Wednesday, August 15, 2018 2:31 PM
  • This is the query I use:

    SELECT
    	[EventDateLocal] = DATEADD(hh,DATEDIFF(hh,GETUTCDATE(), GETDATE()),aud.event_time), 
    	aud.server_instance_name,
    	ActionName = CASE WHEN act.action_id IS NULL THEN act2.name ELSE act.[name] END,
    	cm.class_type_desc,
    	aud.database_name,
    	aud.schema_name,
    	aud.object_name,
    	aud.statement,
    	additional_information = CAST(aud.additional_information AS XML),
    	aud.session_server_principal_name,
    	aud.server_principal_name,
    	aud.database_principal_name,
    	aud.target_server_principal_name,
    	aud.target_database_principal_name,
    	aud.file_name,
    	aud.audit_file_offset,
    	aud.sequence_number,
    	aud.succeeded,
    	aud.session_id
    FROM sys.fn_get_audit_file ('{filename}',default,default) aud
    	INNER JOIN sys.dm_audit_class_type_map cm
    		ON cm.class_type = aud.class_type
    	LEFT OUTER JOIN sys.dm_audit_actions act
    		ON act.action_id = aud.action_id
    			AND act.class_desc = cm.securable_class_desc
    	LEFT OUTER JOIN sys.dm_audit_actions act2
    		ON act2.action_id = aud.action_id
    			AND act2.class_desc = cm.class_type_desc

    • Marked as answer by dydoria Wednesday, August 15, 2018 5:09 PM
    Wednesday, August 15, 2018 2:34 PM

All replies

  • that is sys.types view its referring to I guess

    https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-types-transact-sql?view=sql-server-2017

    It contains details on system and user defined data types


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by dydoria Wednesday, August 15, 2018 5:10 PM
    Wednesday, August 15, 2018 1:20 PM
  • Perhaps the system catalog view "sys.types"?


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, August 15, 2018 1:20 PM
  • You are missing the schema name in your report, it is critical.

    Wednesday, August 15, 2018 1:31 PM
  • Thanks! I thought all objects in the database should be in 'sys.objects', but I couldn't find a view named 'types'.


    Doria

    Wednesday, August 15, 2018 2:10 PM
  • Yes, that's right!

    Doria

    Wednesday, August 15, 2018 2:11 PM
  • I tried to but I couldn't figure out a relationship on system tables to report it.

    SELECT DISTINCT
    LEFT(LOWER(SDAC.class_type_desc), 40) AS class_type_desc,
    LEFT(LOWER(AUDI.object_name), 40) AS object_name,
    LEFT(LOWER(SDAA.name), 30) AS operation_desc
    FROM fn_get_audit_file(N'O:\AccessObjectAudit\*', NULL, NULL) AS AUDI
    INNER JOIN sys.dm_audit_class_type_map SDAC
    ON AUDI.class_type = SDAC.class_type
    INNER JOIN sys.dm_audit_actions AS SDAA
    ON SDAA.action_id = AUDI.action_id AND SDAA.class_desc = SDAC.securable_class_desc
    ORDER BY 1, 2, 3;
    GO


    Doria

    Wednesday, August 15, 2018 2:19 PM
  • Fn_get_audot_file also returns a column named schema_name. So just add that to the columns you return:

    ...

    LEFT(LOWER(AUDI.object_name), 40) AS object_name,

    LEFT(LOWER(AUDI.schema_name), 40) AS schema_name,

    ...

    Btw, I wouldn't like those LOWER in my report, but I assume you have your reasons... :-)


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by dydoria Wednesday, August 15, 2018 5:10 PM
    Wednesday, August 15, 2018 2:31 PM
  • This is the query I use:

    SELECT
    	[EventDateLocal] = DATEADD(hh,DATEDIFF(hh,GETUTCDATE(), GETDATE()),aud.event_time), 
    	aud.server_instance_name,
    	ActionName = CASE WHEN act.action_id IS NULL THEN act2.name ELSE act.[name] END,
    	cm.class_type_desc,
    	aud.database_name,
    	aud.schema_name,
    	aud.object_name,
    	aud.statement,
    	additional_information = CAST(aud.additional_information AS XML),
    	aud.session_server_principal_name,
    	aud.server_principal_name,
    	aud.database_principal_name,
    	aud.target_server_principal_name,
    	aud.target_database_principal_name,
    	aud.file_name,
    	aud.audit_file_offset,
    	aud.sequence_number,
    	aud.succeeded,
    	aud.session_id
    FROM sys.fn_get_audit_file ('{filename}',default,default) aud
    	INNER JOIN sys.dm_audit_class_type_map cm
    		ON cm.class_type = aud.class_type
    	LEFT OUTER JOIN sys.dm_audit_actions act
    		ON act.action_id = aud.action_id
    			AND act.class_desc = cm.securable_class_desc
    	LEFT OUTER JOIN sys.dm_audit_actions act2
    		ON act2.action_id = aud.action_id
    			AND act2.class_desc = cm.class_type_desc

    • Marked as answer by dydoria Wednesday, August 15, 2018 5:09 PM
    Wednesday, August 15, 2018 2:34 PM
  • Thanks! I thought all objects in the database should be in 'sys.objects', but I couldn't find a view named 'types'.


    Doria

    All objects are in sys.objects

    but all their datatypes are in sys.types


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, August 15, 2018 3:44 PM
  • But isn't view types an object itself? That's my point.

      Anyway, thanks all for the great help!


    Doria

    Wednesday, August 15, 2018 5:09 PM