Answered by:
EVENTDATA() in DDL trigger always returns dbo as schema for a CLR procedure

Question
-
using Microsoft SQL Server 2012 (SP4).
We've found that the schema name returned by EVENTDATA() will always show as dbo for a CLR procedure, instead of returning the correct schema name.
- Changed type Will_KongMicrosoft contingent staff, Moderator Friday, July 12, 2019 7:15 AM
Answers
-
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
- Proposed as answer by Rachel_WangMicrosoft contingent staff Wednesday, July 24, 2019 6:44 AM
- Marked as answer by jay_o_brown Wednesday, July 24, 2019 1:47 PM
All replies
-
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.- Proposed as answer by Rachel_WangMicrosoft contingent staff Thursday, July 18, 2019 8:24 AM
-
-
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. -
-
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
- Proposed as answer by Rachel_WangMicrosoft contingent staff Wednesday, July 24, 2019 6:44 AM
- Marked as answer by jay_o_brown Wednesday, July 24, 2019 1:47 PM