none
EVENTDATA() in DDL trigger always returns dbo as schema for a CLR procedure RRS feed

 > 

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    I was able to reproduce the issue on SQL 2019 CTP 3.1.

    You can file a bug about the issue on https://feedback.azure.com/forums/908035-sql-server
    That does not mean that the issue will be fixed, as Microsoft have to prioritise their work. But at least they are told about it.

    If this is a blocking issue for you and you need a fix, you would need to open a support case. However, since SQL 2012 is out of mainstream support, Microsoft may tell you to upgrade first...

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, July 23, 2019 9:44 PM
    |

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi jay_o_brown,

     

    Please check the default schema of the database under your current login. If the default schema is 'dbo', please change it and try again.

     

    Also , I try following steps and then execute following script . The Schema will be changed.

     

    1.create a new Login.

    Server role--->public. 

    User Mapping--->choose a database , write user and default schema.  Database role member ship : db_owner\public


    2.login in and then execute following script again in the same database , and the Schema 'dbo' will be changed into user1.

     

    CREATE TRIGGER log   
ON DATABASE   
FOR DDL_DATABASE_LEVEL_EVENTS   
AS  
   SELECT EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)')  
GO  
--Test the trigger.  
CREATE TABLE TestTable (a int);
GO  
--Drop the trigger.  
DROP TRIGGER log  
ON DATABASE;  
GO

    Hope it will help you.

     

    Best Regards,

    Rachel

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 12, 2019 7:39 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    thanks for trying to help.

    The issue only affects CLR SP's, and we expect event data to return the object schema like it does for any other SP. your code creates a regular table for which we event data returned the correct schema before.

    Friday, July 19, 2019 5:12 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi jay_o_brown,

     

    We are sorry that we could not help you test without your actual environment. I'd be happy to give you some advice if you could provide more information about your problem.

     

    By the way , if you could not provide us more information because  of security ，maybe you can ask professional engineer for help, and they will deal with your problem separately and confidentially.

    https://support.microsoft.com/en-us/assistedsupportproducts

     

    Hope it can help you.

     

    Best Regards,

    Rachel

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 23, 2019 7:22 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    I've noticed the problem only occurs on alter (CLR) proc, not on create and drop, see screen shots of event details attached.

    create CLR proc

    alter CLR proc

    Tuesday, July 23, 2019 3:12 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    I was able to reproduce the issue on SQL 2019 CTP 3.1.

    You can file a bug about the issue on https://feedback.azure.com/forums/908035-sql-server
    That does not mean that the issue will be fixed, as Microsoft have to prioritise their work. But at least they are told about it.

    If this is a blocking issue for you and you need a fix, you would need to open a support case. However, since SQL 2012 is out of mainstream support, Microsoft may tell you to upgrade first...

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, July 23, 2019 9:44 PM
    |